Problem description¶

There is a real business scenario involving risk analytics in banking and financial services to minimize the risk of losing money while lending to customers (Loan applications/approvals).

Two types of risks are associated with the bank’s decision:

If the applicant is likely to repay the loan, then not approving the loan results in a loss of business to the company
If the applicant is not likely to repay the loan, i.e. he/she is likely to default, then approving the loan may lead to a financial loss for the company.

The data given below contains the information about the loan application at the time of applying for the loan. It contains two types of scenarios:

The client with payment difficulties: he/she had late payment more than X days on at least one of the first Y instalments of the loan in our sample,----Typically late payments on X days and Y instalments
All other cases: All other cases when the payment is paid on time.

there are four types of decisions that could be taken by the client/company:

Approved: The Company has approved loan Application
Cancelled: The client cancelled the application sometime during approval. Either the client changed her/his mind about the loan or in some cases due to a higher risk of the client he received worse pricing which he did not want.
Refused: The company had rejected the loan (because the client does not meet their requirements etc.).
Unused offer: Loan has been cancelled by the client but on different stages of the process.

Aim : To identify consumers/customers capable of repaying the loan are not being rejected.¶

#### Importing all the necessary libraries 
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns
import warnings
warnings.filterwarnings("ignore")

Data understanding and available files¶

This dataset has 3 files as explained below:

'application_data.csv' contains all the information of the client at the time of application. The data is about whether a client has payment difficulties.
'previous_application.csv' contains information about the client’s previous loan data. It contains the data whether the previous application had been Approved, Cancelled, Refused or Unused offer.
'columns_description.csv' is data dictionary which describes the meaning of the variables.

### Lets import the application data into pandas dataframe
app = pd.read_csv("application_data.csv")
app.head()
## appears to have lot of columns, let know what they are....

app.shape

(307511, 122)

We have (307511, 122) rows and columns, To decide which columns are feasible for risk analytics, we have to know what they are....data!!¶

This can be checked in the columns description.csv file

### checking on datatypes 
app.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 307511 entries, 0 to 307510
Columns: 122 entries, SK_ID_CURR to AMT_REQ_CREDIT_BUREAU_YEAR
dtypes: float64(65), int64(41), object(16)
memory usage: 286.2+ MB

### Columns with object data types
app.select_dtypes(["object"]).columns, len(app.select_dtypes(["object"]).columns)

(Index(['NAME_CONTRACT_TYPE', 'CODE_GENDER', 'FLAG_OWN_CAR', 'FLAG_OWN_REALTY',
        'NAME_TYPE_SUITE', 'NAME_INCOME_TYPE', 'NAME_EDUCATION_TYPE',
        'NAME_FAMILY_STATUS', 'NAME_HOUSING_TYPE', 'OCCUPATION_TYPE',
        'WEEKDAY_APPR_PROCESS_START', 'ORGANIZATION_TYPE', 'FONDKAPREMONT_MODE',
        'HOUSETYPE_MODE', 'WALLSMATERIAL_MODE', 'EMERGENCYSTATE_MODE'],
       dtype='object'), 16)

### Columns with int data types
app.select_dtypes(["int64"]).columns

Index(['SK_ID_CURR', 'TARGET', 'CNT_CHILDREN', 'DAYS_BIRTH', 'DAYS_EMPLOYED',
       'DAYS_ID_PUBLISH', 'FLAG_MOBIL', 'FLAG_EMP_PHONE', 'FLAG_WORK_PHONE',
       'FLAG_CONT_MOBILE', 'FLAG_PHONE', 'FLAG_EMAIL', 'REGION_RATING_CLIENT',
       'REGION_RATING_CLIENT_W_CITY', 'HOUR_APPR_PROCESS_START',
       'REG_REGION_NOT_LIVE_REGION', 'REG_REGION_NOT_WORK_REGION',
       'LIVE_REGION_NOT_WORK_REGION', 'REG_CITY_NOT_LIVE_CITY',
       'REG_CITY_NOT_WORK_CITY', 'LIVE_CITY_NOT_WORK_CITY', 'FLAG_DOCUMENT_2',
       'FLAG_DOCUMENT_3', 'FLAG_DOCUMENT_4', 'FLAG_DOCUMENT_5',
       'FLAG_DOCUMENT_6', 'FLAG_DOCUMENT_7', 'FLAG_DOCUMENT_8',
       'FLAG_DOCUMENT_9', 'FLAG_DOCUMENT_10', 'FLAG_DOCUMENT_11',
       'FLAG_DOCUMENT_12', 'FLAG_DOCUMENT_13', 'FLAG_DOCUMENT_14',
       'FLAG_DOCUMENT_15', 'FLAG_DOCUMENT_16', 'FLAG_DOCUMENT_17',
       'FLAG_DOCUMENT_18', 'FLAG_DOCUMENT_19', 'FLAG_DOCUMENT_20',
       'FLAG_DOCUMENT_21'],
      dtype='object')

### Columns with float data types
app.select_dtypes(["float64"]).columns

Index(['AMT_INCOME_TOTAL', 'AMT_CREDIT', 'AMT_ANNUITY', 'AMT_GOODS_PRICE',
       'REGION_POPULATION_RELATIVE', 'DAYS_REGISTRATION', 'OWN_CAR_AGE',
       'CNT_FAM_MEMBERS', 'EXT_SOURCE_1', 'EXT_SOURCE_2', 'EXT_SOURCE_3',
       'APARTMENTS_AVG', 'BASEMENTAREA_AVG', 'YEARS_BEGINEXPLUATATION_AVG',
       'YEARS_BUILD_AVG', 'COMMONAREA_AVG', 'ELEVATORS_AVG', 'ENTRANCES_AVG',
       'FLOORSMAX_AVG', 'FLOORSMIN_AVG', 'LANDAREA_AVG',
       'LIVINGAPARTMENTS_AVG', 'LIVINGAREA_AVG', 'NONLIVINGAPARTMENTS_AVG',
       'NONLIVINGAREA_AVG', 'APARTMENTS_MODE', 'BASEMENTAREA_MODE',
       'YEARS_BEGINEXPLUATATION_MODE', 'YEARS_BUILD_MODE', 'COMMONAREA_MODE',
       'ELEVATORS_MODE', 'ENTRANCES_MODE', 'FLOORSMAX_MODE', 'FLOORSMIN_MODE',
       'LANDAREA_MODE', 'LIVINGAPARTMENTS_MODE', 'LIVINGAREA_MODE',
       'NONLIVINGAPARTMENTS_MODE', 'NONLIVINGAREA_MODE', 'APARTMENTS_MEDI',
       'BASEMENTAREA_MEDI', 'YEARS_BEGINEXPLUATATION_MEDI', 'YEARS_BUILD_MEDI',
       'COMMONAREA_MEDI', 'ELEVATORS_MEDI', 'ENTRANCES_MEDI', 'FLOORSMAX_MEDI',
       'FLOORSMIN_MEDI', 'LANDAREA_MEDI', 'LIVINGAPARTMENTS_MEDI',
       'LIVINGAREA_MEDI', 'NONLIVINGAPARTMENTS_MEDI', 'NONLIVINGAREA_MEDI',
       'TOTALAREA_MODE', 'OBS_30_CNT_SOCIAL_CIRCLE',
       'DEF_30_CNT_SOCIAL_CIRCLE', 'OBS_60_CNT_SOCIAL_CIRCLE',
       'DEF_60_CNT_SOCIAL_CIRCLE', 'DAYS_LAST_PHONE_CHANGE',
       'AMT_REQ_CREDIT_BUREAU_HOUR', 'AMT_REQ_CREDIT_BUREAU_DAY',
       'AMT_REQ_CREDIT_BUREAU_WEEK', 'AMT_REQ_CREDIT_BUREAU_MON',
       'AMT_REQ_CREDIT_BUREAU_QRT', 'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

### Using verbose will identify all the columns and there data types
app.info(verbose=True)

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 307511 entries, 0 to 307510
Data columns (total 122 columns):
SK_ID_CURR                      int64
TARGET                          int64
NAME_CONTRACT_TYPE              object
CODE_GENDER                     object
FLAG_OWN_CAR                    object
FLAG_OWN_REALTY                 object
CNT_CHILDREN                    int64
AMT_INCOME_TOTAL                float64
AMT_CREDIT                      float64
AMT_ANNUITY                     float64
AMT_GOODS_PRICE                 float64
NAME_TYPE_SUITE                 object
NAME_INCOME_TYPE                object
NAME_EDUCATION_TYPE             object
NAME_FAMILY_STATUS              object
NAME_HOUSING_TYPE               object
REGION_POPULATION_RELATIVE      float64
DAYS_BIRTH                      int64
DAYS_EMPLOYED                   int64
DAYS_REGISTRATION               float64
DAYS_ID_PUBLISH                 int64
OWN_CAR_AGE                     float64
FLAG_MOBIL                      int64
FLAG_EMP_PHONE                  int64
FLAG_WORK_PHONE                 int64
FLAG_CONT_MOBILE                int64
FLAG_PHONE                      int64
FLAG_EMAIL                      int64
OCCUPATION_TYPE                 object
CNT_FAM_MEMBERS                 float64
REGION_RATING_CLIENT            int64
REGION_RATING_CLIENT_W_CITY     int64
WEEKDAY_APPR_PROCESS_START      object
HOUR_APPR_PROCESS_START         int64
REG_REGION_NOT_LIVE_REGION      int64
REG_REGION_NOT_WORK_REGION      int64
LIVE_REGION_NOT_WORK_REGION     int64
REG_CITY_NOT_LIVE_CITY          int64
REG_CITY_NOT_WORK_CITY          int64
LIVE_CITY_NOT_WORK_CITY         int64
ORGANIZATION_TYPE               object
EXT_SOURCE_1                    float64
EXT_SOURCE_2                    float64
EXT_SOURCE_3                    float64
APARTMENTS_AVG                  float64
BASEMENTAREA_AVG                float64
YEARS_BEGINEXPLUATATION_AVG     float64
YEARS_BUILD_AVG                 float64
COMMONAREA_AVG                  float64
ELEVATORS_AVG                   float64
ENTRANCES_AVG                   float64
FLOORSMAX_AVG                   float64
FLOORSMIN_AVG                   float64
LANDAREA_AVG                    float64
LIVINGAPARTMENTS_AVG            float64
LIVINGAREA_AVG                  float64
NONLIVINGAPARTMENTS_AVG         float64
NONLIVINGAREA_AVG               float64
APARTMENTS_MODE                 float64
BASEMENTAREA_MODE               float64
YEARS_BEGINEXPLUATATION_MODE    float64
YEARS_BUILD_MODE                float64
COMMONAREA_MODE                 float64
ELEVATORS_MODE                  float64
ENTRANCES_MODE                  float64
FLOORSMAX_MODE                  float64
FLOORSMIN_MODE                  float64
LANDAREA_MODE                   float64
LIVINGAPARTMENTS_MODE           float64
LIVINGAREA_MODE                 float64
NONLIVINGAPARTMENTS_MODE        float64
NONLIVINGAREA_MODE              float64
APARTMENTS_MEDI                 float64
BASEMENTAREA_MEDI               float64
YEARS_BEGINEXPLUATATION_MEDI    float64
YEARS_BUILD_MEDI                float64
COMMONAREA_MEDI                 float64
ELEVATORS_MEDI                  float64
ENTRANCES_MEDI                  float64
FLOORSMAX_MEDI                  float64
FLOORSMIN_MEDI                  float64
LANDAREA_MEDI                   float64
LIVINGAPARTMENTS_MEDI           float64
LIVINGAREA_MEDI                 float64
NONLIVINGAPARTMENTS_MEDI        float64
NONLIVINGAREA_MEDI              float64
FONDKAPREMONT_MODE              object
HOUSETYPE_MODE                  object
TOTALAREA_MODE                  float64
WALLSMATERIAL_MODE              object
EMERGENCYSTATE_MODE             object
OBS_30_CNT_SOCIAL_CIRCLE        float64
DEF_30_CNT_SOCIAL_CIRCLE        float64
OBS_60_CNT_SOCIAL_CIRCLE        float64
DEF_60_CNT_SOCIAL_CIRCLE        float64
DAYS_LAST_PHONE_CHANGE          float64
FLAG_DOCUMENT_2                 int64
FLAG_DOCUMENT_3                 int64
FLAG_DOCUMENT_4                 int64
FLAG_DOCUMENT_5                 int64
FLAG_DOCUMENT_6                 int64
FLAG_DOCUMENT_7                 int64
FLAG_DOCUMENT_8                 int64
FLAG_DOCUMENT_9                 int64
FLAG_DOCUMENT_10                int64
FLAG_DOCUMENT_11                int64
FLAG_DOCUMENT_12                int64
FLAG_DOCUMENT_13                int64
FLAG_DOCUMENT_14                int64
FLAG_DOCUMENT_15                int64
FLAG_DOCUMENT_16                int64
FLAG_DOCUMENT_17                int64
FLAG_DOCUMENT_18                int64
FLAG_DOCUMENT_19                int64
FLAG_DOCUMENT_20                int64
FLAG_DOCUMENT_21                int64
AMT_REQ_CREDIT_BUREAU_HOUR      float64
AMT_REQ_CREDIT_BUREAU_DAY       float64
AMT_REQ_CREDIT_BUREAU_WEEK      float64
AMT_REQ_CREDIT_BUREAU_MON       float64
AMT_REQ_CREDIT_BUREAU_QRT       float64
AMT_REQ_CREDIT_BUREAU_YEAR      float64
dtypes: float64(65), int64(41), object(16)
memory usage: 286.2+ MB

### Lets check the descriptive statistics of the dataframe i.e. out of 122 columns we should have 106 columns for statistics info
app.describe()

Based on the data information, we have a glance of data type, info and typical statistic info, next step is to clean the data and choose columns of interest further to analyze the data¶

## Checking on missing values percentage 
null_per = app.isnull().sum()/len(app)*100
null_per

SK_ID_CURR                       0.000000
TARGET                           0.000000
NAME_CONTRACT_TYPE               0.000000
CODE_GENDER                      0.000000
FLAG_OWN_CAR                     0.000000
FLAG_OWN_REALTY                  0.000000
CNT_CHILDREN                     0.000000
AMT_INCOME_TOTAL                 0.000000
AMT_CREDIT                       0.000000
AMT_ANNUITY                      0.003902
AMT_GOODS_PRICE                  0.090403
NAME_TYPE_SUITE                  0.420148
NAME_INCOME_TYPE                 0.000000
NAME_EDUCATION_TYPE              0.000000
NAME_FAMILY_STATUS               0.000000
NAME_HOUSING_TYPE                0.000000
REGION_POPULATION_RELATIVE       0.000000
DAYS_BIRTH                       0.000000
DAYS_EMPLOYED                    0.000000
DAYS_REGISTRATION                0.000000
DAYS_ID_PUBLISH                  0.000000
OWN_CAR_AGE                     65.990810
FLAG_MOBIL                       0.000000
FLAG_EMP_PHONE                   0.000000
FLAG_WORK_PHONE                  0.000000
FLAG_CONT_MOBILE                 0.000000
FLAG_PHONE                       0.000000
FLAG_EMAIL                       0.000000
OCCUPATION_TYPE                 31.345545
CNT_FAM_MEMBERS                  0.000650
REGION_RATING_CLIENT             0.000000
REGION_RATING_CLIENT_W_CITY      0.000000
WEEKDAY_APPR_PROCESS_START       0.000000
HOUR_APPR_PROCESS_START          0.000000
REG_REGION_NOT_LIVE_REGION       0.000000
REG_REGION_NOT_WORK_REGION       0.000000
LIVE_REGION_NOT_WORK_REGION      0.000000
REG_CITY_NOT_LIVE_CITY           0.000000
REG_CITY_NOT_WORK_CITY           0.000000
LIVE_CITY_NOT_WORK_CITY          0.000000
ORGANIZATION_TYPE                0.000000
EXT_SOURCE_1                    56.381073
EXT_SOURCE_2                     0.214626
EXT_SOURCE_3                    19.825307
APARTMENTS_AVG                  50.749729
BASEMENTAREA_AVG                58.515956
YEARS_BEGINEXPLUATATION_AVG     48.781019
YEARS_BUILD_AVG                 66.497784
COMMONAREA_AVG                  69.872297
ELEVATORS_AVG                   53.295980
ENTRANCES_AVG                   50.348768
FLOORSMAX_AVG                   49.760822
FLOORSMIN_AVG                   67.848630
LANDAREA_AVG                    59.376738
LIVINGAPARTMENTS_AVG            68.354953
LIVINGAREA_AVG                  50.193326
NONLIVINGAPARTMENTS_AVG         69.432963
NONLIVINGAREA_AVG               55.179164
APARTMENTS_MODE                 50.749729
BASEMENTAREA_MODE               58.515956
YEARS_BEGINEXPLUATATION_MODE    48.781019
YEARS_BUILD_MODE                66.497784
COMMONAREA_MODE                 69.872297
ELEVATORS_MODE                  53.295980
ENTRANCES_MODE                  50.348768
FLOORSMAX_MODE                  49.760822
FLOORSMIN_MODE                  67.848630
LANDAREA_MODE                   59.376738
LIVINGAPARTMENTS_MODE           68.354953
LIVINGAREA_MODE                 50.193326
NONLIVINGAPARTMENTS_MODE        69.432963
NONLIVINGAREA_MODE              55.179164
APARTMENTS_MEDI                 50.749729
BASEMENTAREA_MEDI               58.515956
YEARS_BEGINEXPLUATATION_MEDI    48.781019
YEARS_BUILD_MEDI                66.497784
COMMONAREA_MEDI                 69.872297
ELEVATORS_MEDI                  53.295980
ENTRANCES_MEDI                  50.348768
FLOORSMAX_MEDI                  49.760822
FLOORSMIN_MEDI                  67.848630
LANDAREA_MEDI                   59.376738
LIVINGAPARTMENTS_MEDI           68.354953
LIVINGAREA_MEDI                 50.193326
NONLIVINGAPARTMENTS_MEDI        69.432963
NONLIVINGAREA_MEDI              55.179164
FONDKAPREMONT_MODE              68.386172
HOUSETYPE_MODE                  50.176091
TOTALAREA_MODE                  48.268517
WALLSMATERIAL_MODE              50.840783
EMERGENCYSTATE_MODE             47.398304
OBS_30_CNT_SOCIAL_CIRCLE         0.332021
DEF_30_CNT_SOCIAL_CIRCLE         0.332021
OBS_60_CNT_SOCIAL_CIRCLE         0.332021
DEF_60_CNT_SOCIAL_CIRCLE         0.332021
DAYS_LAST_PHONE_CHANGE           0.000325
FLAG_DOCUMENT_2                  0.000000
FLAG_DOCUMENT_3                  0.000000
FLAG_DOCUMENT_4                  0.000000
FLAG_DOCUMENT_5                  0.000000
FLAG_DOCUMENT_6                  0.000000
FLAG_DOCUMENT_7                  0.000000
FLAG_DOCUMENT_8                  0.000000
FLAG_DOCUMENT_9                  0.000000
FLAG_DOCUMENT_10                 0.000000
FLAG_DOCUMENT_11                 0.000000
FLAG_DOCUMENT_12                 0.000000
FLAG_DOCUMENT_13                 0.000000
FLAG_DOCUMENT_14                 0.000000
FLAG_DOCUMENT_15                 0.000000
FLAG_DOCUMENT_16                 0.000000
FLAG_DOCUMENT_17                 0.000000
FLAG_DOCUMENT_18                 0.000000
FLAG_DOCUMENT_19                 0.000000
FLAG_DOCUMENT_20                 0.000000
FLAG_DOCUMENT_21                 0.000000
AMT_REQ_CREDIT_BUREAU_HOUR      13.501631
AMT_REQ_CREDIT_BUREAU_DAY       13.501631
AMT_REQ_CREDIT_BUREAU_WEEK      13.501631
AMT_REQ_CREDIT_BUREAU_MON       13.501631
AMT_REQ_CREDIT_BUREAU_QRT       13.501631
AMT_REQ_CREDIT_BUREAU_YEAR      13.501631
dtype: float64

### As we are unable to check on all the rows and columns, we will change the settings in pandas
pd.set_option('display.max_columns', 400)
pd.set_option('display.max_rows', 400)

null_per = app.isnull().sum()/len(app)*100
null_per

SK_ID_CURR                       0.000000
TARGET                           0.000000
NAME_CONTRACT_TYPE               0.000000
CODE_GENDER                      0.000000
FLAG_OWN_CAR                     0.000000
FLAG_OWN_REALTY                  0.000000
CNT_CHILDREN                     0.000000
AMT_INCOME_TOTAL                 0.000000
AMT_CREDIT                       0.000000
AMT_ANNUITY                      0.003902
AMT_GOODS_PRICE                  0.090403
NAME_TYPE_SUITE                  0.420148
NAME_INCOME_TYPE                 0.000000
NAME_EDUCATION_TYPE              0.000000
NAME_FAMILY_STATUS               0.000000
NAME_HOUSING_TYPE                0.000000
REGION_POPULATION_RELATIVE       0.000000
DAYS_BIRTH                       0.000000
DAYS_EMPLOYED                    0.000000
DAYS_REGISTRATION                0.000000
DAYS_ID_PUBLISH                  0.000000
OWN_CAR_AGE                     65.990810
FLAG_MOBIL                       0.000000
FLAG_EMP_PHONE                   0.000000
FLAG_WORK_PHONE                  0.000000
FLAG_CONT_MOBILE                 0.000000
FLAG_PHONE                       0.000000
FLAG_EMAIL                       0.000000
OCCUPATION_TYPE                 31.345545
CNT_FAM_MEMBERS                  0.000650
REGION_RATING_CLIENT             0.000000
REGION_RATING_CLIENT_W_CITY      0.000000
WEEKDAY_APPR_PROCESS_START       0.000000
HOUR_APPR_PROCESS_START          0.000000
REG_REGION_NOT_LIVE_REGION       0.000000
REG_REGION_NOT_WORK_REGION       0.000000
LIVE_REGION_NOT_WORK_REGION      0.000000
REG_CITY_NOT_LIVE_CITY           0.000000
REG_CITY_NOT_WORK_CITY           0.000000
LIVE_CITY_NOT_WORK_CITY          0.000000
ORGANIZATION_TYPE                0.000000
EXT_SOURCE_1                    56.381073
EXT_SOURCE_2                     0.214626
EXT_SOURCE_3                    19.825307
APARTMENTS_AVG                  50.749729
BASEMENTAREA_AVG                58.515956
YEARS_BEGINEXPLUATATION_AVG     48.781019
YEARS_BUILD_AVG                 66.497784
COMMONAREA_AVG                  69.872297
ELEVATORS_AVG                   53.295980
ENTRANCES_AVG                   50.348768
FLOORSMAX_AVG                   49.760822
FLOORSMIN_AVG                   67.848630
LANDAREA_AVG                    59.376738
LIVINGAPARTMENTS_AVG            68.354953
LIVINGAREA_AVG                  50.193326
NONLIVINGAPARTMENTS_AVG         69.432963
NONLIVINGAREA_AVG               55.179164
APARTMENTS_MODE                 50.749729
BASEMENTAREA_MODE               58.515956
YEARS_BEGINEXPLUATATION_MODE    48.781019
YEARS_BUILD_MODE                66.497784
COMMONAREA_MODE                 69.872297
ELEVATORS_MODE                  53.295980
ENTRANCES_MODE                  50.348768
FLOORSMAX_MODE                  49.760822
FLOORSMIN_MODE                  67.848630
LANDAREA_MODE                   59.376738
LIVINGAPARTMENTS_MODE           68.354953
LIVINGAREA_MODE                 50.193326
NONLIVINGAPARTMENTS_MODE        69.432963
NONLIVINGAREA_MODE              55.179164
APARTMENTS_MEDI                 50.749729
BASEMENTAREA_MEDI               58.515956
YEARS_BEGINEXPLUATATION_MEDI    48.781019
YEARS_BUILD_MEDI                66.497784
COMMONAREA_MEDI                 69.872297
ELEVATORS_MEDI                  53.295980
ENTRANCES_MEDI                  50.348768
FLOORSMAX_MEDI                  49.760822
FLOORSMIN_MEDI                  67.848630
LANDAREA_MEDI                   59.376738
LIVINGAPARTMENTS_MEDI           68.354953
LIVINGAREA_MEDI                 50.193326
NONLIVINGAPARTMENTS_MEDI        69.432963
NONLIVINGAREA_MEDI              55.179164
FONDKAPREMONT_MODE              68.386172
HOUSETYPE_MODE                  50.176091
TOTALAREA_MODE                  48.268517
WALLSMATERIAL_MODE              50.840783
EMERGENCYSTATE_MODE             47.398304
OBS_30_CNT_SOCIAL_CIRCLE         0.332021
DEF_30_CNT_SOCIAL_CIRCLE         0.332021
OBS_60_CNT_SOCIAL_CIRCLE         0.332021
DEF_60_CNT_SOCIAL_CIRCLE         0.332021
DAYS_LAST_PHONE_CHANGE           0.000325
FLAG_DOCUMENT_2                  0.000000
FLAG_DOCUMENT_3                  0.000000
FLAG_DOCUMENT_4                  0.000000
FLAG_DOCUMENT_5                  0.000000
FLAG_DOCUMENT_6                  0.000000
FLAG_DOCUMENT_7                  0.000000
FLAG_DOCUMENT_8                  0.000000
FLAG_DOCUMENT_9                  0.000000
FLAG_DOCUMENT_10                 0.000000
FLAG_DOCUMENT_11                 0.000000
FLAG_DOCUMENT_12                 0.000000
FLAG_DOCUMENT_13                 0.000000
FLAG_DOCUMENT_14                 0.000000
FLAG_DOCUMENT_15                 0.000000
FLAG_DOCUMENT_16                 0.000000
FLAG_DOCUMENT_17                 0.000000
FLAG_DOCUMENT_18                 0.000000
FLAG_DOCUMENT_19                 0.000000
FLAG_DOCUMENT_20                 0.000000
FLAG_DOCUMENT_21                 0.000000
AMT_REQ_CREDIT_BUREAU_HOUR      13.501631
AMT_REQ_CREDIT_BUREAU_DAY       13.501631
AMT_REQ_CREDIT_BUREAU_WEEK      13.501631
AMT_REQ_CREDIT_BUREAU_MON       13.501631
AMT_REQ_CREDIT_BUREAU_QRT       13.501631
AMT_REQ_CREDIT_BUREAU_YEAR      13.501631
dtype: float64

app.columns.get_loc("APARTMENTS_AVG"), app.columns.get_loc("EMERGENCYSTATE_MODE")

(44, 90)

As the living apartment space and parameters has no direct relationship for loan application process and columns are having lot of missing entries > 40%, we can drop those columns from 44 to 90 indexes¶

Dropping the columns from 44 to 90¶

app.drop(app.iloc[:,44:91].columns, axis =1, inplace = True)
app.shape

(307511, 75)

### Lets check the missing value entries
app.isnull().sum()/len(app)*100

SK_ID_CURR                      0.000000
TARGET                          0.000000
NAME_CONTRACT_TYPE              0.000000
CODE_GENDER                     0.000000
FLAG_OWN_CAR                    0.000000
FLAG_OWN_REALTY                 0.000000
CNT_CHILDREN                    0.000000
AMT_INCOME_TOTAL                0.000000
AMT_CREDIT                      0.000000
AMT_ANNUITY                     0.003902
AMT_GOODS_PRICE                 0.090403
NAME_TYPE_SUITE                 0.420148
NAME_INCOME_TYPE                0.000000
NAME_EDUCATION_TYPE             0.000000
NAME_FAMILY_STATUS              0.000000
NAME_HOUSING_TYPE               0.000000
REGION_POPULATION_RELATIVE      0.000000
DAYS_BIRTH                      0.000000
DAYS_EMPLOYED                   0.000000
DAYS_REGISTRATION               0.000000
DAYS_ID_PUBLISH                 0.000000
OWN_CAR_AGE                    65.990810
FLAG_MOBIL                      0.000000
FLAG_EMP_PHONE                  0.000000
FLAG_WORK_PHONE                 0.000000
FLAG_CONT_MOBILE                0.000000
FLAG_PHONE                      0.000000
FLAG_EMAIL                      0.000000
OCCUPATION_TYPE                31.345545
CNT_FAM_MEMBERS                 0.000650
REGION_RATING_CLIENT            0.000000
REGION_RATING_CLIENT_W_CITY     0.000000
WEEKDAY_APPR_PROCESS_START      0.000000
HOUR_APPR_PROCESS_START         0.000000
REG_REGION_NOT_LIVE_REGION      0.000000
REG_REGION_NOT_WORK_REGION      0.000000
LIVE_REGION_NOT_WORK_REGION     0.000000
REG_CITY_NOT_LIVE_CITY          0.000000
REG_CITY_NOT_WORK_CITY          0.000000
LIVE_CITY_NOT_WORK_CITY         0.000000
ORGANIZATION_TYPE               0.000000
EXT_SOURCE_1                   56.381073
EXT_SOURCE_2                    0.214626
EXT_SOURCE_3                   19.825307
OBS_30_CNT_SOCIAL_CIRCLE        0.332021
DEF_30_CNT_SOCIAL_CIRCLE        0.332021
OBS_60_CNT_SOCIAL_CIRCLE        0.332021
DEF_60_CNT_SOCIAL_CIRCLE        0.332021
DAYS_LAST_PHONE_CHANGE          0.000325
FLAG_DOCUMENT_2                 0.000000
FLAG_DOCUMENT_3                 0.000000
FLAG_DOCUMENT_4                 0.000000
FLAG_DOCUMENT_5                 0.000000
FLAG_DOCUMENT_6                 0.000000
FLAG_DOCUMENT_7                 0.000000
FLAG_DOCUMENT_8                 0.000000
FLAG_DOCUMENT_9                 0.000000
FLAG_DOCUMENT_10                0.000000
FLAG_DOCUMENT_11                0.000000
FLAG_DOCUMENT_12                0.000000
FLAG_DOCUMENT_13                0.000000
FLAG_DOCUMENT_14                0.000000
FLAG_DOCUMENT_15                0.000000
FLAG_DOCUMENT_16                0.000000
FLAG_DOCUMENT_17                0.000000
FLAG_DOCUMENT_18                0.000000
FLAG_DOCUMENT_19                0.000000
FLAG_DOCUMENT_20                0.000000
FLAG_DOCUMENT_21                0.000000
AMT_REQ_CREDIT_BUREAU_HOUR     13.501631
AMT_REQ_CREDIT_BUREAU_DAY      13.501631
AMT_REQ_CREDIT_BUREAU_WEEK     13.501631
AMT_REQ_CREDIT_BUREAU_MON      13.501631
AMT_REQ_CREDIT_BUREAU_QRT      13.501631
AMT_REQ_CREDIT_BUREAU_YEAR     13.501631
dtype: float64

### Lets only check the columns names with missing entries using isna() and any()
app.columns[app.isna().any()]

Index(['AMT_ANNUITY', 'AMT_GOODS_PRICE', 'NAME_TYPE_SUITE', 'OWN_CAR_AGE',
       'OCCUPATION_TYPE', 'CNT_FAM_MEMBERS', 'EXT_SOURCE_1', 'EXT_SOURCE_2',
       'EXT_SOURCE_3', 'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'AMT_REQ_CREDIT_BUREAU_HOUR',
       'AMT_REQ_CREDIT_BUREAU_DAY', 'AMT_REQ_CREDIT_BUREAU_WEEK',
       'AMT_REQ_CREDIT_BUREAU_MON', 'AMT_REQ_CREDIT_BUREAU_QRT',
       'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

app[app.columns[app.isna().any()]]

### Lets handle the missing values one by one
app.AMT_ANNUITY.isnull().sum()/len(app)*100

0.003902299429939092

app.AMT_ANNUITY.value_counts()

9000.0      6385
13500.0     5514
6750.0      2279
10125.0     2035
37800.0     1602
11250.0     1459
26217.0     1453
20250.0     1345
12375.0     1339
31653.0     1269
26640.0     1236
22500.0     1234
16875.0     1213
36459.0     1191
33025.5      938
26446.5      916
22018.5      904
23773.5      875
25537.5      836
26316.0      829
31261.5      822
21906.0      800
7875.0       741
40320.0      703
21888.0      688
32602.5      687
15750.0      684
27000.0      681
19867.5      653
33750.0      652
29970.0      643
21775.5      643
30528.0      626
45000.0      570
32895.0      564
14350.5      564
14751.0      551
23494.5      540
42790.5      527
26086.5      507
29250.0      506
23931.0      501
31630.5      499
31522.5      495
24939.0      491
16456.5      490
19125.0      471
21109.5      466
13963.5      462
30078.0      450
28408.5      449
45333.0      441
46084.5      436
67500.0      431
16011.0      419
24543.0      418
35937.0      418
25321.5      417
24246.0      406
32125.5      400
22599.0      400
20979.0      395
26509.5      393
37669.5      386
20677.5      384
22050.0      383
51948.0      379
38263.5      378
24592.5      376
17694.0      368
18000.0      367
32472.0      360
24750.0      359
16164.0      358
42642.0      357
36864.0      354
38331.0      351
40063.5      350
31500.0      342
32274.0      340
34587.0      340
28215.0      339
27324.0      338
34911.0      331
23850.0      331
27679.5      330
21672.0      328
27423.0      326
15241.5      325
25407.0      324
52452.0      322
16573.5      318
31464.0      318
48631.5      317
41692.5      313
51543.0      311
25969.5      310
19737.0      306
39604.5      305
23107.5      302
47794.5      300
17167.5      300
45954.0      298
17095.5      297
16713.0      296
16155.0      291
31153.5      290
26284.5      289
22977.0      284
36292.5      284
17905.5      282
30280.5      279
27193.5      278
14625.0      274
18040.5      273
32337.0      272
35392.5      269
24412.5      269
23643.0      268
39474.0      265
35523.0      264
22527.0      263
36328.5      261
48586.5      259
29839.5      256
16852.5      252
21919.5      250
21375.0      248
33394.5      248
17775.0      247
35554.5      247
12001.5      246
21865.5      245
30717.0      245
35685.0      244
33376.5      242
34596.0      240
26833.5      237
19134.0      237
13045.5      237
17149.5      236
27558.0      236
41629.5      234
22437.0      234
17338.5      233
32017.5      232
13090.5      232
68643.0      231
24259.5      230
26145.0      230
16047.0      229
29862.0      229
28728.0      229
17563.5      228
26770.5      228
26743.5      228
47322.0      228
38133.0      227
56092.5      226
38686.5      225
11074.5      220
24561.0      220
36553.5      218
23305.5      217
40500.0      217
19998.0      212
22383.0      211
43312.5      209
42142.5      209
19705.5      204
37255.5      204
23800.5      203
36000.0      202
12694.5      202
30204.0      201
25960.5      199
27189.0      198
28597.5      197
38250.0      197
20295.0      197
20596.5      197
30073.5      196
27153.0      196
25078.5      195
36423.0      193
28570.5      193
19953.0      192
21996.0      192
16096.5      192
26901.0      192
35617.5      191
17527.5      191
19039.5      190
34465.5      189
53460.0      188
44694.0      188
31018.5      188
37125.0      188
18643.5      187
25578.0      184
            ... 
48523.5        1
22675.5        1
60633.0        1
4144.5         1
61807.5        1
80527.5        1
48357.0        1
120532.5       1
80541.0        1
80545.5        1
65529.0        1
73822.5        1
44122.5        1
56295.0        1
166572.0       1
32841.0        1
67176.0        1
73903.5        1
73948.5        1
11187.0        1
60318.0        1
24921.0        1
73966.5        1
3060.0         1
71280.0        1
66006.0        1
39150.0        1
97011.0        1
57991.5        1
37854.0        1
106969.5       1
83529.0        1
61816.5        1
3145.5         1
73912.5        1
4063.5         1
40441.5        1
12339.0        1
49036.5        1
81310.5        1
3352.5         1
258025.5       1
89154.0        1
73665.0        1
70119.0        1
49014.0        1
58437.0        1
98050.5        1
71874.0        1
39042.0        1
126342.0       1
58279.5        1
56425.5        1
4545.0         1
72715.5        1
81364.5        1
5004.0         1
60655.5        1
58032.0        1
59238.0        1
40698.0        1
59661.0        1
38295.0        1
7177.5         1
72868.5        1
43668.0        1
8874.0         1
81274.5        1
3541.5         1
23013.0        1
15979.5        1
81283.5        1
61753.5        1
72810.0        1
145615.5       1
56115.0        1
9090.0         1
67599.0        1
97888.5        1
60363.0        1
49081.5        1
51399.0        1
91516.5        1
72580.5        1
19620.0        1
41742.0        1
11061.0        1
9369.0         1
36283.5        1
61623.0        1
53257.5        1
72553.5        1
81508.5        1
72544.5        1
61726.5        1
4729.5         1
41238.0        1
72598.5        1
79353.0        1
98158.5        1
46638.0        1
213160.5       1
49054.5        1
60894.0        1
91552.5        1
72661.5        1
4261.5         1
47259.0        1
106533.0       1
66213.0        1
28881.0        1
98739.0        1
55557.0        1
84168.0        1
58306.5        1
6471.0         1
29155.5        1
7839.0         1
9094.5         1
73354.5        1
3204.0         1
28246.5        1
73719.0        1
48330.0        1
53392.5        1
60480.0        1
63508.5        1
54009.0        1
69228.0        1
48802.5        1
80977.5        1
80995.5        1
123565.5       1
48820.5        1
73165.5        1
46116.0        1
34164.0        1
75231.0        1
56497.5        1
8761.5         1
73255.5        1
74718.0        1
97528.5        1
68472.0        1
73341.0        1
108805.5       1
116266.5       1
102384.0       1
83475.0        1
83916.0        1
62253.0        1
97537.5        1
47565.0        1
52281.0        1
58144.5        1
56799.0        1
73291.5        1
40450.5        1
48829.5        1
45891.0        1
40621.5        1
61780.5        1
146002.5       1
53388.0        1
36414.0        1
72985.5        1
45873.0        1
73692.0        1
47263.5        1
61767.0        1
71910.0        1
72949.5        1
36472.5        1
81202.5        1
40603.5        1
47205.0        1
39087.0        1
81225.0        1
49932.0        1
73003.5        1
53505.0        1
58243.5        1
73053.0        1
81049.5        1
62703.0        1
45877.5        1
56470.5        1
62383.5        1
63499.5        1
81094.5        1
73048.5        1
91732.5        1
56538.0        1
54297.0        1
81108.0        1
15210.0        1
50265.0        1
73012.5        1
40558.5        1
4437.0         1
Name: AMT_ANNUITY, Length: 13672, dtype: int64

The formula for determining the present value of an annuity is PV = dollar amount of an individual annuity payment multiplied by P = PMT * [1 – [ (1 / 1+r)^n] / r] where: P = Present value of your annuity stream. PMT = Dollar amount of each payment. r = Discount or interest rate, if we want to impute some values here in this columns we should know payments and interest rate which we dont have, Using central measures of tendency will lead to ambiguity so we may drop those missing entries and its hardly 0.03%.¶

app.AMT_ANNUITY.isnull().value_counts()

False    307499
True         12
Name: AMT_ANNUITY, dtype: int64

app[app["AMT_ANNUITY"].isnull()].head()
### Lets think about dropping the rows in this column later

### Checking next column 
app.AMT_GOODS_PRICE.isnull().value_counts()
### We have 278 entries i.e 9% of missing entries

False    307233
True        278
Name: AMT_GOODS_PRICE, dtype: int64

app["AMT_GOODS_PRICE"].mean(), app["AMT_GOODS_PRICE"].median(), app["AMT_GOODS_PRICE"].mode()

(538396.2074288895, 450000.0, 0    450000.0
 dtype: float64)

app[app["AMT_GOODS_PRICE"].isnull()]

If we observe most of NaN values for this column, they have a common contract type i.e Revolving loans are not for purchasing in item, A revolving loan facility is a form of credit issued by a financial institution that provides the borrower with the ability to draw down or withdraw, repay, and withdraw again. A revolving loan is considered a flexible financing tool due to its repayment and re-borrowing accommodations. Since we don't know actual values , we can impute zero value as we dont know...¶

app["AMT_GOODS_PRICE"].fillna(value = 0, inplace = True)

app.AMT_GOODS_PRICE.isnull().sum()

0

## Checking next column 'NAME_TYPE_SUITE'
app.NAME_TYPE_SUITE.isna().value_counts()

False    306219
True       1292
Name: NAME_TYPE_SUITE, dtype: int64

ap1  = app[app["NAME_TYPE_SUITE"].isnull()]
ap1

ap1["NAME_CONTRACT_TYPE"].value_counts()

Cash loans         1007
Revolving loans     285
Name: NAME_CONTRACT_TYPE, dtype: int64

Most of the missing entries with NAME TYPE SUITE is contract based on cash loans.

A cash loan is a loan which is received by the borrower in cash. This sets it apart from overdraft loans, installment loans and leases. Cash loans may be given to a private individual as a personal loan or to a business as a business loan

app["NAME_TYPE_SUITE"].value_counts()

Unaccompanied      248526
Family              40149
Spouse, partner     11370
Children             3267
Other_B              1770
Other_A               866
Group of people       271
Name: NAME_TYPE_SUITE, dtype: int64

### This column is a categorical variable, we can do the imputation with mode
app["NAME_TYPE_SUITE"].fillna(value = app["NAME_TYPE_SUITE"].mode()[0], inplace = True)

app["NAME_TYPE_SUITE"].isnull().sum()

0

app.columns[app.isna().any()]

Index(['AMT_ANNUITY', 'OWN_CAR_AGE', 'OCCUPATION_TYPE', 'CNT_FAM_MEMBERS',
       'EXT_SOURCE_1', 'EXT_SOURCE_2', 'EXT_SOURCE_3',
       'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'AMT_REQ_CREDIT_BUREAU_HOUR',
       'AMT_REQ_CREDIT_BUREAU_DAY', 'AMT_REQ_CREDIT_BUREAU_WEEK',
       'AMT_REQ_CREDIT_BUREAU_MON', 'AMT_REQ_CREDIT_BUREAU_QRT',
       'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

#### Checking on next column 'OWN_CAR_AGE'
app["OWN_CAR_AGE"].isnull().value_counts()

True     202929
False    104582
Name: OWN_CAR_AGE, dtype: int64

app["OWN_CAR_AGE"].isnull().sum()/len(app)*100

65.9908100848425

app["OWN_CAR_AGE"].unique()

array([nan, 26., 17.,  8., 23.,  7., 14.,  1.,  3., 13., 10.,  2., 21.,
       20., 22., 65.,  5., 11.,  9.,  4., 15., 12., 25.,  6., 64.,  0.,
       27., 19., 28., 18., 16., 30., 36., 24., 39., 29., 40., 41., 31.,
       42., 37., 35., 34., 33., 54., 38., 32., 43., 55., 49., 44., 63.,
       46., 45., 48., 56., 51., 57., 69., 50., 47., 91., 52.])

We have 65% of missing entries with 'OWN CAR AGE ' column, may be people have cars let check¶

app[app["FLAG_OWN_CAR"] == 'N'][["FLAG_OWN_CAR", "OWN_CAR_AGE"]].head()

### It appears the most of the people have no cars....we can impute the car age as zero since they dont own car
app["OWN_CAR_AGE"].fillna(value = 0, inplace =True)

app["OWN_CAR_AGE"].isnull().sum()

0

app.columns[app.isnull().any()]

Index(['AMT_ANNUITY', 'OCCUPATION_TYPE', 'CNT_FAM_MEMBERS', 'EXT_SOURCE_1',
       'EXT_SOURCE_2', 'EXT_SOURCE_3', 'OBS_30_CNT_SOCIAL_CIRCLE',
       'DEF_30_CNT_SOCIAL_CIRCLE', 'OBS_60_CNT_SOCIAL_CIRCLE',
       'DEF_60_CNT_SOCIAL_CIRCLE', 'DAYS_LAST_PHONE_CHANGE',
       'AMT_REQ_CREDIT_BUREAU_HOUR', 'AMT_REQ_CREDIT_BUREAU_DAY',
       'AMT_REQ_CREDIT_BUREAU_WEEK', 'AMT_REQ_CREDIT_BUREAU_MON',
       'AMT_REQ_CREDIT_BUREAU_QRT', 'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

app["OCCUPATION_TYPE"].unique()

array(['Laborers', 'Core staff', 'Accountants', 'Managers', nan,
       'Drivers', 'Sales staff', 'Cleaning staff', 'Cooking staff',
       'Private service staff', 'Medicine staff', 'Security staff',
       'High skill tech staff', 'Waiters/barmen staff',
       'Low-skill Laborers', 'Realty agents', 'Secretaries', 'IT staff',
       'HR staff'], dtype=object)

app[app["OCCUPATION_TYPE"].isnull()]

So we have lot of null values with occupation type, let check the percentage

app["OCCUPATION_TYPE"].isnull().sum()/len(app)*100

31.345545362604916

So we have nearly 30% of the data missing, let check if they fall to which NAME INCOME TYPE

ap2  = app[app["OCCUPATION_TYPE"].isnull()][["NAME_INCOME_TYPE"]]
ap2

ap2.NAME_INCOME_TYPE.value_counts()

Pensioner               55357
Working                 24920
Commercial associate    12297
State servant            3787
Unemployed                 22
Student                     5
Businessman                 2
Maternity leave             1
Name: NAME_INCOME_TYPE, dtype: int64

As we have lot of "pensioners" in NAME_INCOME_TYPE for missing entries of Occupation type, we can impute the missing values with occupation type as "Retired/others", lets not impute the values as the numbers are high.

app.columns[app.isna().any()]

Index(['AMT_ANNUITY', 'OCCUPATION_TYPE', 'CNT_FAM_MEMBERS', 'EXT_SOURCE_1',
       'EXT_SOURCE_2', 'EXT_SOURCE_3', 'OBS_30_CNT_SOCIAL_CIRCLE',
       'DEF_30_CNT_SOCIAL_CIRCLE', 'OBS_60_CNT_SOCIAL_CIRCLE',
       'DEF_60_CNT_SOCIAL_CIRCLE', 'DAYS_LAST_PHONE_CHANGE',
       'AMT_REQ_CREDIT_BUREAU_HOUR', 'AMT_REQ_CREDIT_BUREAU_DAY',
       'AMT_REQ_CREDIT_BUREAU_WEEK', 'AMT_REQ_CREDIT_BUREAU_MON',
       'AMT_REQ_CREDIT_BUREAU_QRT', 'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

app[app.CNT_FAM_MEMBERS.isnull()]

as this is just a set of 2 rows of missing entries, we can drop these rows...

app.drop(labels =[41982,187348], axis =0, inplace =True)

app.CNT_FAM_MEMBERS.isnull().sum()

0

app.columns[app.isna().any()]

Index(['AMT_ANNUITY', 'OCCUPATION_TYPE', 'EXT_SOURCE_1', 'EXT_SOURCE_2',
       'EXT_SOURCE_3', 'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'AMT_REQ_CREDIT_BUREAU_HOUR',
       'AMT_REQ_CREDIT_BUREAU_DAY', 'AMT_REQ_CREDIT_BUREAU_WEEK',
       'AMT_REQ_CREDIT_BUREAU_MON', 'AMT_REQ_CREDIT_BUREAU_QRT',
       'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

Now we still have lot of columns with null values , lets visualize and see by how much % we have those values

### Checking on NaN values in respoective columns
app.isnull().sum().sort_values(ascending= False)

EXT_SOURCE_1                   173377
OCCUPATION_TYPE                 96391
EXT_SOURCE_3                    60964
AMT_REQ_CREDIT_BUREAU_YEAR      41518
AMT_REQ_CREDIT_BUREAU_MON       41518
AMT_REQ_CREDIT_BUREAU_WEEK      41518
AMT_REQ_CREDIT_BUREAU_DAY       41518
AMT_REQ_CREDIT_BUREAU_HOUR      41518
AMT_REQ_CREDIT_BUREAU_QRT       41518
DEF_60_CNT_SOCIAL_CIRCLE         1021
OBS_60_CNT_SOCIAL_CIRCLE         1021
DEF_30_CNT_SOCIAL_CIRCLE         1021
OBS_30_CNT_SOCIAL_CIRCLE         1021
EXT_SOURCE_2                      660
AMT_ANNUITY                        12
DAYS_LAST_PHONE_CHANGE              1
OWN_CAR_AGE                         0
FLAG_MOBIL                          0
DAYS_ID_PUBLISH                     0
FLAG_WORK_PHONE                     0
FLAG_CONT_MOBILE                    0
FLAG_PHONE                          0
FLAG_EMAIL                          0
FLAG_EMP_PHONE                      0
DAYS_BIRTH                          0
DAYS_REGISTRATION                   0
AMT_CREDIT                          0
TARGET                              0
NAME_CONTRACT_TYPE                  0
CODE_GENDER                         0
FLAG_OWN_CAR                        0
FLAG_OWN_REALTY                     0
CNT_CHILDREN                        0
AMT_INCOME_TOTAL                    0
AMT_GOODS_PRICE                     0
DAYS_EMPLOYED                       0
NAME_TYPE_SUITE                     0
NAME_INCOME_TYPE                    0
NAME_EDUCATION_TYPE                 0
NAME_FAMILY_STATUS                  0
NAME_HOUSING_TYPE                   0
REGION_POPULATION_RELATIVE          0
REGION_RATING_CLIENT                0
CNT_FAM_MEMBERS                     0
REG_CITY_NOT_LIVE_CITY              0
REGION_RATING_CLIENT_W_CITY         0
WEEKDAY_APPR_PROCESS_START          0
FLAG_DOCUMENT_21                    0
FLAG_DOCUMENT_20                    0
FLAG_DOCUMENT_19                    0
FLAG_DOCUMENT_18                    0
FLAG_DOCUMENT_17                    0
FLAG_DOCUMENT_16                    0
FLAG_DOCUMENT_15                    0
FLAG_DOCUMENT_14                    0
FLAG_DOCUMENT_13                    0
FLAG_DOCUMENT_12                    0
FLAG_DOCUMENT_11                    0
FLAG_DOCUMENT_10                    0
FLAG_DOCUMENT_9                     0
FLAG_DOCUMENT_8                     0
FLAG_DOCUMENT_7                     0
FLAG_DOCUMENT_6                     0
FLAG_DOCUMENT_5                     0
FLAG_DOCUMENT_4                     0
FLAG_DOCUMENT_3                     0
FLAG_DOCUMENT_2                     0
ORGANIZATION_TYPE                   0
LIVE_CITY_NOT_WORK_CITY             0
REG_CITY_NOT_WORK_CITY              0
LIVE_REGION_NOT_WORK_REGION         0
REG_REGION_NOT_WORK_REGION          0
REG_REGION_NOT_LIVE_REGION          0
HOUR_APPR_PROCESS_START             0
SK_ID_CURR                          0
dtype: int64

## Lets visualize the same
NaN_cols = app.isnull().sum().sort_values(ascending = False)
x = NaN_cols.head(10)
x

EXT_SOURCE_1                  173377
OCCUPATION_TYPE                96391
EXT_SOURCE_3                   60964
AMT_REQ_CREDIT_BUREAU_YEAR     41518
AMT_REQ_CREDIT_BUREAU_MON      41518
AMT_REQ_CREDIT_BUREAU_WEEK     41518
AMT_REQ_CREDIT_BUREAU_DAY      41518
AMT_REQ_CREDIT_BUREAU_HOUR     41518
AMT_REQ_CREDIT_BUREAU_QRT      41518
DEF_60_CNT_SOCIAL_CIRCLE        1021
dtype: int64

sns.barplot(x.index, y = x/len(app)*100)
plt.title("NaN values in %")
plt.show();

NaN_cols.index[:5]

Index(['EXT_SOURCE_1', 'OCCUPATION_TYPE', 'EXT_SOURCE_3',
       'AMT_REQ_CREDIT_BUREAU_YEAR', 'AMT_REQ_CREDIT_BUREAU_MON'],
      dtype='object')

May be we can remove the columns have NaN entries more than 40%, i.e. column with EXT_SOURCE_1

app.drop(labels = ["EXT_SOURCE_1"], axis =1 , inplace = True)

app.columns[app.isna().any()]

Index(['AMT_ANNUITY', 'OCCUPATION_TYPE', 'EXT_SOURCE_2', 'EXT_SOURCE_3',
       'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'AMT_REQ_CREDIT_BUREAU_HOUR',
       'AMT_REQ_CREDIT_BUREAU_DAY', 'AMT_REQ_CREDIT_BUREAU_WEEK',
       'AMT_REQ_CREDIT_BUREAU_MON', 'AMT_REQ_CREDIT_BUREAU_QRT',
       'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

x1 = app.isnull().sum().sort_values(ascending= False)
x1

OCCUPATION_TYPE                96391
EXT_SOURCE_3                   60964
AMT_REQ_CREDIT_BUREAU_YEAR     41518
AMT_REQ_CREDIT_BUREAU_MON      41518
AMT_REQ_CREDIT_BUREAU_WEEK     41518
AMT_REQ_CREDIT_BUREAU_DAY      41518
AMT_REQ_CREDIT_BUREAU_HOUR     41518
AMT_REQ_CREDIT_BUREAU_QRT      41518
DEF_60_CNT_SOCIAL_CIRCLE        1021
OBS_60_CNT_SOCIAL_CIRCLE        1021
DEF_30_CNT_SOCIAL_CIRCLE        1021
OBS_30_CNT_SOCIAL_CIRCLE        1021
EXT_SOURCE_2                     660
AMT_ANNUITY                       12
DAYS_LAST_PHONE_CHANGE             1
FLAG_EMP_PHONE                     0
OWN_CAR_AGE                        0
FLAG_MOBIL                         0
DAYS_REGISTRATION                  0
FLAG_WORK_PHONE                    0
FLAG_CONT_MOBILE                   0
FLAG_PHONE                         0
FLAG_EMAIL                         0
DAYS_ID_PUBLISH                    0
REGION_POPULATION_RELATIVE         0
DAYS_EMPLOYED                      0
DAYS_BIRTH                         0
TARGET                             0
NAME_CONTRACT_TYPE                 0
CODE_GENDER                        0
FLAG_OWN_CAR                       0
FLAG_OWN_REALTY                    0
CNT_CHILDREN                       0
AMT_INCOME_TOTAL                   0
AMT_CREDIT                         0
AMT_GOODS_PRICE                    0
NAME_TYPE_SUITE                    0
NAME_INCOME_TYPE                   0
NAME_EDUCATION_TYPE                0
NAME_FAMILY_STATUS                 0
NAME_HOUSING_TYPE                  0
REGION_RATING_CLIENT               0
CNT_FAM_MEMBERS                    0
LIVE_REGION_NOT_WORK_REGION        0
REGION_RATING_CLIENT_W_CITY        0
WEEKDAY_APPR_PROCESS_START         0
FLAG_DOCUMENT_21                   0
FLAG_DOCUMENT_20                   0
FLAG_DOCUMENT_19                   0
FLAG_DOCUMENT_18                   0
FLAG_DOCUMENT_17                   0
FLAG_DOCUMENT_16                   0
FLAG_DOCUMENT_15                   0
FLAG_DOCUMENT_14                   0
FLAG_DOCUMENT_13                   0
FLAG_DOCUMENT_12                   0
FLAG_DOCUMENT_11                   0
FLAG_DOCUMENT_10                   0
FLAG_DOCUMENT_9                    0
FLAG_DOCUMENT_8                    0
FLAG_DOCUMENT_7                    0
FLAG_DOCUMENT_6                    0
FLAG_DOCUMENT_5                    0
FLAG_DOCUMENT_4                    0
FLAG_DOCUMENT_3                    0
FLAG_DOCUMENT_2                    0
ORGANIZATION_TYPE                  0
LIVE_CITY_NOT_WORK_CITY            0
REG_CITY_NOT_WORK_CITY             0
REG_CITY_NOT_LIVE_CITY             0
REG_REGION_NOT_WORK_REGION         0
REG_REGION_NOT_LIVE_REGION         0
HOUR_APPR_PROCESS_START            0
SK_ID_CURR                         0
dtype: int64

app.columns[app.isna().any()]

Index(['AMT_ANNUITY', 'OCCUPATION_TYPE', 'EXT_SOURCE_2', 'EXT_SOURCE_3',
       'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'AMT_REQ_CREDIT_BUREAU_HOUR',
       'AMT_REQ_CREDIT_BUREAU_DAY', 'AMT_REQ_CREDIT_BUREAU_WEEK',
       'AMT_REQ_CREDIT_BUREAU_MON', 'AMT_REQ_CREDIT_BUREAU_QRT',
       'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

app.filter(['AMT_REQ_CREDIT_BUREAU_HOUR',
       'AMT_REQ_CREDIT_BUREAU_DAY', 'AMT_REQ_CREDIT_BUREAU_WEEK',
       'AMT_REQ_CREDIT_BUREAU_MON', 'AMT_REQ_CREDIT_BUREAU_QRT',
       'AMT_REQ_CREDIT_BUREAU_YEAR']).mode()

As we can see the above columns could be imputed with mode as they refer to Number of enquiries to Credit Bureau about the client.

### we can also use user defined function for imputation
def mode_imp(df, col):
    return df[col].fillna(value = df[col].mode(), inplace = True)

app.isnull().sum().sort_values(ascending = False)

OCCUPATION_TYPE                96391
EXT_SOURCE_3                   60964
AMT_REQ_CREDIT_BUREAU_YEAR     41518
AMT_REQ_CREDIT_BUREAU_MON      41518
AMT_REQ_CREDIT_BUREAU_WEEK     41518
AMT_REQ_CREDIT_BUREAU_DAY      41518
AMT_REQ_CREDIT_BUREAU_HOUR     41518
AMT_REQ_CREDIT_BUREAU_QRT      41518
DEF_60_CNT_SOCIAL_CIRCLE        1021
OBS_60_CNT_SOCIAL_CIRCLE        1021
DEF_30_CNT_SOCIAL_CIRCLE        1021
OBS_30_CNT_SOCIAL_CIRCLE        1021
EXT_SOURCE_2                     660
AMT_ANNUITY                       12
DAYS_LAST_PHONE_CHANGE             1
FLAG_EMP_PHONE                     0
OWN_CAR_AGE                        0
FLAG_MOBIL                         0
DAYS_REGISTRATION                  0
FLAG_WORK_PHONE                    0
FLAG_CONT_MOBILE                   0
FLAG_PHONE                         0
FLAG_EMAIL                         0
DAYS_ID_PUBLISH                    0
REGION_POPULATION_RELATIVE         0
DAYS_EMPLOYED                      0
DAYS_BIRTH                         0
TARGET                             0
NAME_CONTRACT_TYPE                 0
CODE_GENDER                        0
FLAG_OWN_CAR                       0
FLAG_OWN_REALTY                    0
CNT_CHILDREN                       0
AMT_INCOME_TOTAL                   0
AMT_CREDIT                         0
AMT_GOODS_PRICE                    0
NAME_TYPE_SUITE                    0
NAME_INCOME_TYPE                   0
NAME_EDUCATION_TYPE                0
NAME_FAMILY_STATUS                 0
NAME_HOUSING_TYPE                  0
REGION_RATING_CLIENT               0
CNT_FAM_MEMBERS                    0
LIVE_REGION_NOT_WORK_REGION        0
REGION_RATING_CLIENT_W_CITY        0
WEEKDAY_APPR_PROCESS_START         0
FLAG_DOCUMENT_21                   0
FLAG_DOCUMENT_20                   0
FLAG_DOCUMENT_19                   0
FLAG_DOCUMENT_18                   0
FLAG_DOCUMENT_17                   0
FLAG_DOCUMENT_16                   0
FLAG_DOCUMENT_15                   0
FLAG_DOCUMENT_14                   0
FLAG_DOCUMENT_13                   0
FLAG_DOCUMENT_12                   0
FLAG_DOCUMENT_11                   0
FLAG_DOCUMENT_10                   0
FLAG_DOCUMENT_9                    0
FLAG_DOCUMENT_8                    0
FLAG_DOCUMENT_7                    0
FLAG_DOCUMENT_6                    0
FLAG_DOCUMENT_5                    0
FLAG_DOCUMENT_4                    0
FLAG_DOCUMENT_3                    0
FLAG_DOCUMENT_2                    0
ORGANIZATION_TYPE                  0
LIVE_CITY_NOT_WORK_CITY            0
REG_CITY_NOT_WORK_CITY             0
REG_CITY_NOT_LIVE_CITY             0
REG_REGION_NOT_WORK_REGION         0
REG_REGION_NOT_LIVE_REGION         0
HOUR_APPR_PROCESS_START            0
SK_ID_CURR                         0
dtype: int64

cols = ['EXT_SOURCE_2',
       'EXT_SOURCE_3', 'OBS_30_CNT_SOCIAL_CIRCLE',
       'DEF_30_CNT_SOCIAL_CIRCLE', 'OBS_60_CNT_SOCIAL_CIRCLE',
       'DEF_60_CNT_SOCIAL_CIRCLE']
app[cols].mode()

As we know that the data of number of days past due(DPD) for the clients social surroundings is just 0.3% of missing data, here also we can go for mode imputation for this columns

mode_imp(app, "OBS_30_CNT_SOCIAL_CIRCLE")

### we can also use user defined function for imputation
def mode_imp(df, col):
    df[col].fillna(value = df[col].mode()[0], inplace = True)

app.isnull().sum()

SK_ID_CURR                         0
TARGET                             0
NAME_CONTRACT_TYPE                 0
CODE_GENDER                        0
FLAG_OWN_CAR                       0
FLAG_OWN_REALTY                    0
CNT_CHILDREN                       0
AMT_INCOME_TOTAL                   0
AMT_CREDIT                         0
AMT_ANNUITY                       12
AMT_GOODS_PRICE                    0
NAME_TYPE_SUITE                    0
NAME_INCOME_TYPE                   0
NAME_EDUCATION_TYPE                0
NAME_FAMILY_STATUS                 0
NAME_HOUSING_TYPE                  0
REGION_POPULATION_RELATIVE         0
DAYS_BIRTH                         0
DAYS_EMPLOYED                      0
DAYS_REGISTRATION                  0
DAYS_ID_PUBLISH                    0
OWN_CAR_AGE                        0
FLAG_MOBIL                         0
FLAG_EMP_PHONE                     0
FLAG_WORK_PHONE                    0
FLAG_CONT_MOBILE                   0
FLAG_PHONE                         0
FLAG_EMAIL                         0
OCCUPATION_TYPE                96391
CNT_FAM_MEMBERS                    0
REGION_RATING_CLIENT               0
REGION_RATING_CLIENT_W_CITY        0
WEEKDAY_APPR_PROCESS_START         0
HOUR_APPR_PROCESS_START            0
REG_REGION_NOT_LIVE_REGION         0
REG_REGION_NOT_WORK_REGION         0
LIVE_REGION_NOT_WORK_REGION        0
REG_CITY_NOT_LIVE_CITY             0
REG_CITY_NOT_WORK_CITY             0
LIVE_CITY_NOT_WORK_CITY            0
ORGANIZATION_TYPE                  0
EXT_SOURCE_2                     660
EXT_SOURCE_3                   60964
OBS_30_CNT_SOCIAL_CIRCLE        1021
DEF_30_CNT_SOCIAL_CIRCLE        1021
OBS_60_CNT_SOCIAL_CIRCLE        1021
DEF_60_CNT_SOCIAL_CIRCLE        1021
DAYS_LAST_PHONE_CHANGE             1
FLAG_DOCUMENT_2                    0
FLAG_DOCUMENT_3                    0
FLAG_DOCUMENT_4                    0
FLAG_DOCUMENT_5                    0
FLAG_DOCUMENT_6                    0
FLAG_DOCUMENT_7                    0
FLAG_DOCUMENT_8                    0
FLAG_DOCUMENT_9                    0
FLAG_DOCUMENT_10                   0
FLAG_DOCUMENT_11                   0
FLAG_DOCUMENT_12                   0
FLAG_DOCUMENT_13                   0
FLAG_DOCUMENT_14                   0
FLAG_DOCUMENT_15                   0
FLAG_DOCUMENT_16                   0
FLAG_DOCUMENT_17                   0
FLAG_DOCUMENT_18                   0
FLAG_DOCUMENT_19                   0
FLAG_DOCUMENT_20                   0
FLAG_DOCUMENT_21                   0
AMT_REQ_CREDIT_BUREAU_HOUR     41518
AMT_REQ_CREDIT_BUREAU_DAY      41518
AMT_REQ_CREDIT_BUREAU_WEEK     41518
AMT_REQ_CREDIT_BUREAU_MON      41518
AMT_REQ_CREDIT_BUREAU_QRT      41518
AMT_REQ_CREDIT_BUREAU_YEAR     41518
dtype: int64

missin_cols = ['EXT_SOURCE_2', 'EXT_SOURCE_3']
app.filter(missin_cols).isnull().sum()/len(app)

EXT_SOURCE_2    0.002146
EXT_SOURCE_3    0.198251
dtype: float64

app["EXT_SOURCE_3"].mean(),app["EXT_SOURCE_3"].median()

(0.5108522580271521, 0.5352762504724826)

With respect to Normalized score from external source, imputation can be challeging as SOURCE_3 has 20% of the data, which we cannot drop those rows however we can impute them with median value as

The median is better suited for skewed distributions to derive at central tendency since it is much more robust and sensible.

Imputation is not done as it is not necessary

## Finally , we only have these columns with missing entries
app.columns[app.isna().any()]

Index(['AMT_ANNUITY', 'OCCUPATION_TYPE', 'EXT_SOURCE_2', 'EXT_SOURCE_3',
       'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'AMT_REQ_CREDIT_BUREAU_HOUR',
       'AMT_REQ_CREDIT_BUREAU_DAY', 'AMT_REQ_CREDIT_BUREAU_WEEK',
       'AMT_REQ_CREDIT_BUREAU_MON', 'AMT_REQ_CREDIT_BUREAU_QRT',
       'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

## As the % is less for both of these columns around 0.1-0.2% , we can drop these rows of entries
app[app["DAYS_LAST_PHONE_CHANGE"].isnull()]

app.drop(labels = [15709], axis =0, inplace = True)

app.columns[app.isna().any()]

Index(['AMT_ANNUITY', 'OCCUPATION_TYPE', 'EXT_SOURCE_2', 'EXT_SOURCE_3',
       'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'AMT_REQ_CREDIT_BUREAU_HOUR', 'AMT_REQ_CREDIT_BUREAU_DAY',
       'AMT_REQ_CREDIT_BUREAU_WEEK', 'AMT_REQ_CREDIT_BUREAU_MON',
       'AMT_REQ_CREDIT_BUREAU_QRT', 'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

app.EXT_SOURCE_2.mean(), app.EXT_SOURCE_2.median(), app.EXT_SOURCE_2.mode()
app.EXT_SOURCE_2.isnull().sum()

659

Now we have dropped some rows/columns and imputed some rows/columns understanding the data, now there are some missing entries that's not necessary for imputation, lets explore the data for any errors , standardization and visualize it clearly...¶

app.columns

Index(['SK_ID_CURR', 'TARGET', 'NAME_CONTRACT_TYPE', 'CODE_GENDER',
       'FLAG_OWN_CAR', 'FLAG_OWN_REALTY', 'CNT_CHILDREN', 'AMT_INCOME_TOTAL',
       'AMT_CREDIT', 'AMT_ANNUITY', 'AMT_GOODS_PRICE', 'NAME_TYPE_SUITE',
       'NAME_INCOME_TYPE', 'NAME_EDUCATION_TYPE', 'NAME_FAMILY_STATUS',
       'NAME_HOUSING_TYPE', 'REGION_POPULATION_RELATIVE', 'DAYS_BIRTH',
       'DAYS_EMPLOYED', 'DAYS_REGISTRATION', 'DAYS_ID_PUBLISH', 'OWN_CAR_AGE',
       'FLAG_MOBIL', 'FLAG_EMP_PHONE', 'FLAG_WORK_PHONE', 'FLAG_CONT_MOBILE',
       'FLAG_PHONE', 'FLAG_EMAIL', 'OCCUPATION_TYPE', 'CNT_FAM_MEMBERS',
       'REGION_RATING_CLIENT', 'REGION_RATING_CLIENT_W_CITY',
       'WEEKDAY_APPR_PROCESS_START', 'HOUR_APPR_PROCESS_START',
       'REG_REGION_NOT_LIVE_REGION', 'REG_REGION_NOT_WORK_REGION',
       'LIVE_REGION_NOT_WORK_REGION', 'REG_CITY_NOT_LIVE_CITY',
       'REG_CITY_NOT_WORK_CITY', 'LIVE_CITY_NOT_WORK_CITY',
       'ORGANIZATION_TYPE', 'EXT_SOURCE_2', 'EXT_SOURCE_3',
       'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'FLAG_DOCUMENT_2', 'FLAG_DOCUMENT_3',
       'FLAG_DOCUMENT_4', 'FLAG_DOCUMENT_5', 'FLAG_DOCUMENT_6',
       'FLAG_DOCUMENT_7', 'FLAG_DOCUMENT_8', 'FLAG_DOCUMENT_9',
       'FLAG_DOCUMENT_10', 'FLAG_DOCUMENT_11', 'FLAG_DOCUMENT_12',
       'FLAG_DOCUMENT_13', 'FLAG_DOCUMENT_14', 'FLAG_DOCUMENT_15',
       'FLAG_DOCUMENT_16', 'FLAG_DOCUMENT_17', 'FLAG_DOCUMENT_18',
       'FLAG_DOCUMENT_19', 'FLAG_DOCUMENT_20', 'FLAG_DOCUMENT_21',
       'AMT_REQ_CREDIT_BUREAU_HOUR', 'AMT_REQ_CREDIT_BUREAU_DAY',
       'AMT_REQ_CREDIT_BUREAU_WEEK', 'AMT_REQ_CREDIT_BUREAU_MON',
       'AMT_REQ_CREDIT_BUREAU_QRT', 'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')

### Lets understand the statistics of the data after overcoming NaN values
app.describe()

It appears like we have data of certain columns with negative values, we have to correct these errors¶

app.shape

(307508, 74)

## we will identify using comprehension
err_cols  = [c for c in app.columns if c.startswith('DAYS')]
err_cols

['DAYS_BIRTH',
 'DAYS_EMPLOYED',
 'DAYS_REGISTRATION',
 'DAYS_ID_PUBLISH',
 'DAYS_LAST_PHONE_CHANGE']

err_cols = ['DAYS_BIRTH','DAYS_EMPLOYED', 'DAYS_REGISTRATION', 'DAYS_ID_PUBLISH']

### We will convert them to absolute values
app[err_cols] = abs(app[err_cols])

app[err_cols].describe()

### Now these are the columns with object datatypes
app.select_dtypes("object").columns

Index(['NAME_CONTRACT_TYPE', 'CODE_GENDER', 'FLAG_OWN_CAR', 'FLAG_OWN_REALTY',
       'NAME_TYPE_SUITE', 'NAME_INCOME_TYPE', 'NAME_EDUCATION_TYPE',
       'NAME_FAMILY_STATUS', 'NAME_HOUSING_TYPE', 'OCCUPATION_TYPE',
       'WEEKDAY_APPR_PROCESS_START', 'ORGANIZATION_TYPE'],
      dtype='object')

Lets analyse the data and perform distinct analysis for the variables based on data types¶

loan_per = app.NAME_CONTRACT_TYPE.value_counts()/len(app)*100
loan_per

Cash loans         90.479272
Revolving loans     9.520728
Name: NAME_CONTRACT_TYPE, dtype: float64

As we can see most of loan processed data is for cash loans (90%) relative Revolving loans (9.5%)¶

app["TARGET"].value_counts()/len(app)*100

0    91.927039
1     8.072961
Name: TARGET, dtype: float64

sns.barplot(x = 'TARGET', y = app['TARGET'].value_counts(), data = app, ci = None)
plt.show();

app["TARGET"].value_counts(normalize = True)

0    0.91927
1    0.08073
Name: TARGET, dtype: float64

As we can observe the client with solid payment issues are around 8% rest of the 92% are repaying well, there are imbalances may be we will understand why certain people are having payment issues by separating them into another dataframe¶

### Saving the corrected file copy so that , we need not run all the cells above to perform data_anaysis
app.to_csv('rev1_app.csv', index=False)

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-2-0854615f4f63> in <module>
      1 ### Saving the corrected file copy so that , we need not run all the cells above to perform data_anaysis
----> 2 app.to_csv('rev1_app.csv', index=False)

NameError: name 'app' is not defined

app1 = pd.read_csv("rev1_app.csv")
app1.head()

pd.set_option('display.max_columns', 400)
pd.set_option('display.max_rows', 400)

app1.select_dtypes("object").columns

Index(['NAME_CONTRACT_TYPE', 'CODE_GENDER', 'FLAG_OWN_CAR', 'FLAG_OWN_REALTY',
       'NAME_TYPE_SUITE', 'NAME_INCOME_TYPE', 'NAME_EDUCATION_TYPE',
       'NAME_FAMILY_STATUS', 'NAME_HOUSING_TYPE', 'OCCUPATION_TYPE',
       'WEEKDAY_APPR_PROCESS_START', 'ORGANIZATION_TYPE'],
      dtype='object')

loan_per = app1.NAME_CONTRACT_TYPE.value_counts()/len(app1)*100
loan_per

Cash loans         90.479272
Revolving loans     9.520728
Name: NAME_CONTRACT_TYPE, dtype: float64

sns.set(font_scale=1.4)
loan_per.plot(kind='barh', figsize=(7, 6), rot=0)
plt.xlabel("Loan type", labelpad=12)
plt.ylabel("Loan counts", labelpad=12)
plt.title("Loans processed", y=1.02);

Most of the loans are sponsored for cash loans (90%) and 8% for Revolving loans

app1.CODE_GENDER.value_counts()

F      202447
M      105057
XNA         4
Name: CODE_GENDER, dtype: int64

app1.loc[app1.CODE_GENDER == "XNA"]

Based on column CODE_GENDER, we can drop the rows as the data is 'XNA' i.e may be the description is Indeterminate/Intersex/Unspecified.

app1["CODE_GENDER"].drop(labels =[35656,38565,83380,189637], axis =0, inplace = True)

app1.CODE_GENDER.value_counts(), app1.shape

(F    202447
 M    105057
 Name: CODE_GENDER, dtype: int64, (307508, 74))

sns.set(font_scale=1.5)
app1["CODE_GENDER"].value_counts().plot(kind='bar', figsize=(8, 6), rot=0)
plt.xlabel("GENDER", labelpad=10)
plt.ylabel("COUNTS", labelpad=10)
plt.title("TOTAL COUNTS", y=1.02);

More Loan applications is observed for Females yet we dont know who has payment issues

app1["FLAG_OWN_CAR"].value_counts()

N    202922
Y    104586
Name: FLAG_OWN_CAR, dtype: int64

sns.set(font_scale=1.5)
app1["FLAG_OWN_CAR"].value_counts().plot(kind='bar', figsize=(8, 6), rot=0)
plt.xlabel("OWNS_CAR", labelpad=10)
plt.ylabel("COUNTS", labelpad=10)
plt.title("TOTAL COUNTS", y=1.02);

Most of the people dont owns cars....¶

app1["FLAG_OWN_REALTY"].value_counts()

Y    213309
N     94199
Name: FLAG_OWN_REALTY, dtype: int64

sns.set(font_scale=1.5)
app1["FLAG_OWN_REALTY"].value_counts().plot(kind='bar', figsize=(8, 6), rot=0)
plt.xlabel("OWNS_REAL_ESTATE", labelpad=10)
plt.ylabel("NUMBERS", labelpad=10)
plt.title("TOTAL NUMBERS", y=1.02);

Most of the people own real estate , could be the issues for payment? let' see¶

app1.select_dtypes("object").columns

Index(['NAME_CONTRACT_TYPE', 'CODE_GENDER', 'FLAG_OWN_CAR', 'FLAG_OWN_REALTY',
       'NAME_TYPE_SUITE', 'NAME_INCOME_TYPE', 'NAME_EDUCATION_TYPE',
       'NAME_FAMILY_STATUS', 'NAME_HOUSING_TYPE', 'OCCUPATION_TYPE',
       'WEEKDAY_APPR_PROCESS_START', 'ORGANIZATION_TYPE'],
      dtype='object')

app1["NAME_TYPE_SUITE"].value_counts()

Unaccompanied      249815
Family              40149
Spouse, partner     11370
Children             3267
Other_B              1770
Other_A               866
Group of people       271
Name: NAME_TYPE_SUITE, dtype: int64

sns.set(font_scale=1.5)
app1["NAME_TYPE_SUITE"].value_counts().plot(kind='barh', figsize=(8, 6), rot=0)
plt.xlabel("NUMBERS", labelpad=10)
plt.ylabel("SUITE OPTIONS", labelpad=10)
plt.title("TOTAL NUMBERS", y=1.02);

Most of the loans processed are with unaccompanied clients¶

app1["NAME_INCOME_TYPE"].value_counts()

Working                 158773
Commercial associate     71615
Pensioner                55362
State servant            21703
Unemployed                  22
Student                     18
Businessman                 10
Maternity leave              5
Name: NAME_INCOME_TYPE, dtype: int64

per_income_typ= app1["NAME_INCOME_TYPE"].value_counts()/len(app1)*100
per_income_typ.plot(kind='bar', figsize=(10, 8), rot=90 )
plt.xlabel("INCOME_TYPE", labelpad=10)
plt.ylabel("%COUNT", labelpad=10)
plt.title("INCOME_TYPE_%COUNTS", y=1.0);

Most of the people are from working profiles > 50% for loan applications¶

app1["NAME_EDUCATION_TYPE"].value_counts()

Secondary / secondary special    218391
Higher education                  74862
Incomplete higher                 10276
Lower secondary                    3815
Academic degree                     164
Name: NAME_EDUCATION_TYPE, dtype: int64

per_income_typ= app1["NAME_EDUCATION_TYPE"].value_counts()/len(app1)*100
per_income_typ.plot(kind='bar', figsize=(10, 8), rot=90 )
plt.xlabel("EDUCATION_TYPE", labelpad=10)
plt.ylabel("%COUNT", labelpad=10)
plt.title("EDUCATION_TYPE_%COUNTS", y=1.0);

Most of the observations around 70% are with Secondary/Secondary special education¶

app1["NAME_FAMILY_STATUS"].value_counts().plot(kind='bar', figsize=(10, 8), rot=90);

app1["NAME_HOUSING_TYPE"].value_counts().plot(kind='bar', figsize=(8, 6), rot=90);

app1["OCCUPATION_TYPE"].value_counts().plot(kind='bar', figsize=(8, 6), rot=90);

app1["WEEKDAY_APPR_PROCESS_START"].value_counts().plot(kind='bar');

Most of the loan applications were done in start of the week on average.¶

app1["ORGANIZATION_TYPE"].value_counts().plot(kind='bar', figsize=(15, 12), rot=90)
plt.xlabel("ORGANIZATION_TYPE", labelpad=8)
plt.ylabel("COUNTS", labelpad=8)
plt.title("ORGANIZATION_TYPE_COUNTS", y=1.0);

#### Most of the loans are decided based on income of client, assets, years of working period/timeline of loan, 
### loan credit amount...most of them, we will identify the amount based columns and analyse the data here
col_amt = [c for c in app1.columns if c.startswith('AMT')]
col_amt

['AMT_INCOME_TOTAL',
 'AMT_CREDIT',
 'AMT_ANNUITY',
 'AMT_GOODS_PRICE',
 'AMT_REQ_CREDIT_BUREAU_HOUR',
 'AMT_REQ_CREDIT_BUREAU_DAY',
 'AMT_REQ_CREDIT_BUREAU_WEEK',
 'AMT_REQ_CREDIT_BUREAU_MON',
 'AMT_REQ_CREDIT_BUREAU_QRT',
 'AMT_REQ_CREDIT_BUREAU_YEAR']

### Lets check the income columns for any outliers
sns.boxplot(app1["AMT_INCOME_TOTAL"].value_counts())

<matplotlib.axes._subplots.AxesSubplot at 0x12859528a58>

Based on the box plot for income total, lets perfom binning to identify any outliers better and get better understanding¶

### Binnning the values and converting them numerical to categorical
app1["AMT_INCOME_CAT"] = pd.qcut(app1.AMT_INCOME_TOTAL,q=[0,0.2,0.4,0.6,0.8,1.0],labels = ["VLOW","LOW","MEDIOCRE","HIGH","VHIGH"])
app1.AMT_INCOME_CAT.head()

0     HIGH
1    VHIGH
2     VLOW
3      LOW
4      LOW
Name: AMT_INCOME_CAT, dtype: category
Categories (5, object): [VLOW < LOW < MEDIOCRE < HIGH < VHIGH]

### Binning values for income credit loan
app1["AMT_CREDIT_CAT"] = pd.qcut(app1.AMT_CREDIT,q=[0,0.2,0.4,0.6,0.8,1.0],labels = ["VLOW","LOW","MEDIOCRE","HIGH","VHIGH"])
app1.AMT_CREDIT_CAT.head()

0         LOW
1       VHIGH
2        VLOW
3         LOW
4    MEDIOCRE
Name: AMT_CREDIT_CAT, dtype: category
Categories (5, object): [VLOW < LOW < MEDIOCRE < HIGH < VHIGH]

app1.AMT_CREDIT_CAT.value_counts().plot(kind= "bar")

<matplotlib.axes._subplots.AxesSubplot at 0x1285900acf8>

we see that people have more numbers for very low and high salaries relative to other groups....¶

app1.AMT_INCOME_CAT.value_counts().plot(kind= "bar")

<matplotlib.axes._subplots.AxesSubplot at 0x12858f2b6d8>

People have more counts for low and high profile salaries i.e. in synch with amount credited¶

Checking on columns that have days employed.or starting with days..etc so that we know people with high numbers of days being employed¶

col_day = [c for c in app1.columns if c.startswith('DAYS')]
col_day

['DAYS_BIRTH',
 'DAYS_EMPLOYED',
 'DAYS_REGISTRATION',
 'DAYS_ID_PUBLISH',
 'DAYS_LAST_PHONE_CHANGE']

### Lets check on the age group for the data clients age in days
app1.DAYS_BIRTH.min(), app1.DAYS_BIRTH.max(), app1.DAYS_BIRTH.mean()

(7489.0, 25229.0, 16037.041559894376)

### Lets do binning of these values as well to know in which age group they fall
app1["DAYS_BIRTH_CAT"] = pd.cut(app1.DAYS_BIRTH,bins=[0,5475,9125,16425,25550],labels = ["YOUNG","TEENAGE","MIDDLE-AGE","SENIORS"])
app1["DAYS_BIRTH_CAT"].head()

0    MIDDLE-AGE
1       SENIORS
2       SENIORS
3       SENIORS
4       SENIORS
Name: DAYS_BIRTH_CAT, dtype: category
Categories (4, object): [YOUNG < TEENAGE < MIDDLE-AGE < SENIORS]

app1.DAYS_BIRTH_CAT.value_counts().plot(kind="bar");

### Thats for binning values, lets identify outliers in the data
sns.boxplot(y = app1["AMT_INCOME_TOTAL"])
plt.title("AMT_INCOME_DISTRIBUTION")
plt.show();

### Lets try with plotly plots as the plot above is not clear, importing library
import plotly
import plotly.express as px

fig = px.box(app1, y="AMT_INCOME_TOTAL",title='AMT_INCOME_DISTRIBUTION' )
fig.show()

One of the value 117M is definitely an outlier¶

sns.boxplot(y = app1["DAYS_BIRTH"])
plt.show();

sns.boxplot(y = app1["AMT_ANNUITY"])
plt.show();

The value above 250K is definitely an outlier¶

sns.boxplot(y = app1["DAYS_EMPLOYED"])
plt.show();

Values above 300000 is defintely outlier¶

sns.boxplot(y = app1["DAYS_REGISTRATION"])
plt.show();

Now to understand why target-1 is having payment issues and target-0 is runnings with no issues, we have split the data into two groups to analyze more better¶

app1.TARGET.value_counts()

0    282683
1     24825
Name: TARGET, dtype: int64

data_imbalance  = app1.TARGET.value_counts()/len(app1)*100
data_imbalance

0    91.927039
1     8.072961
Name: TARGET, dtype: float64

colors = sns.color_palette('pastel')
plt.pie(data_imbalance, labels = ["Nopayissue", "payissue"],colors = colors, autopct='%.f%%')
plt.title("Data Imbalance")
plt.show();

Dividing the dataset into two groups based on target values¶

app1[app1["TARGET"] == 0]

### we can also use loc for separate the dataset
t0 = app1.loc[app1["TARGET"] == 0]   ### No payment issues
t1 = app1.loc[app1["TARGET"] == 1]   #### Payment issues

### Cheking on payment issues with income groups
t1.shape, t0.shape

((24825, 77), (282683, 77))

sns.distplot(t0.AMT_INCOME_TOTAL, norm_hist=True)
#plt.ylim(0, 4)
plt.xlim(0, 0.2e7)
plt.show();

sns.distplot(t1.AMT_INCOME_TOTAL, norm_hist=True)
# control x and y limits
#plt.ylim(0, 4)
plt.xlim(0, 1e7)
plt.show();

t1[t1["AMT_INCOME_TOTAL"] > 11700000]

t1["AMT_INCOME_TOTAL"].drop(labels = [12840], axis =0, inplace = True)

fig = px.box(t1, y="AMT_INCOME_TOTAL",title='AMT_INCOME_DISTRIBUTION' )
fig.show()

t0[t0["AMT_INCOME_TOTAL"] > 15000000]

t0["AMT_INCOME_TOTAL"].drop(labels = [203690], axis =0, inplace = True)

fig = px.box(t0, y="AMT_INCOME_TOTAL",title='AMT_INCOME_DISTRIBUTION' )
fig.show()

We will start with univariate analysis of target-1 group¶

### Checking on gender distribution of payment issues
t1["CODE_GENDER"].value_counts().plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("CODE_GENDER", labelpad=8)
plt.ylabel("COUNTS", labelpad=8)
plt.title("GENDER_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

t0["CODE_GENDER"].value_counts().plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("CODE_GENDER", labelpad=8)
plt.ylabel("COUNTS", labelpad=8)
plt.title("GENDER_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

Observations¶

Based on gender distribution, we observe that payment issues are observed more for female in target0 group.
At the same time, females are also paying well with no-payment issues in target-1 group.
Data is more saturated with female payments and non-payments issues

### Checking on income type distribution of payment issues
per_income_type = t1["NAME_INCOME_TYPE"].value_counts()/len(t1)*100
per_income_type.plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("INCOME_TYPE", labelpad=8)
plt.ylabel("PERCENTAGE", labelpad=8)
plt.title("INCOME_TYPE_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

Observations¶

We can observe that 60% payment issues are from working professionals.
We can observe that 21-23% payment issues are from Commercial associate.
We can observe that 10% and 5% payment issues from pensioners and state servant.

#### Observations in t0 group
per_income_type = t0["NAME_INCOME_TYPE"].value_counts()/len(t0)*100
per_income_type.plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("INCOME_TYPE", labelpad=8)
plt.ylabel("PERCENTAGE", labelpad=8)
plt.title("INCOME_TYPE_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

### Checking on contract type distribution for payment issues
per_contract_type = t1["NAME_CONTRACT_TYPE"].value_counts()/len(t1)*100
per_contract_type.plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("CONTRACT_TYPE", labelpad=8)
plt.ylabel("PERCENTAGE", labelpad=8)
plt.title("CONTRACT_TYPE_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

Observations¶

Most of the payment issues are with cash loans

### Checking on education type distribution for payment issues
per_edu_type = t1["NAME_EDUCATION_TYPE"].value_counts()/len(t1)*100
per_edu_type.plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("CONTRACT_TYPE", labelpad=8)
plt.ylabel("PERCENTAGE", labelpad=8)
plt.title("EDUCATION_TYPE_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

### Checking on Family status distribution for payment issues
per_fstat_type = t1["NAME_FAMILY_STATUS"].value_counts()/len(t1)*100
per_fstat_type.plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("FAMILY_STATUS_TYPE", labelpad=8)
plt.ylabel("PERCENTAGE", labelpad=8)
plt.title("FAMILY_STATUS_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

### Checking on housing type distribution for payment issues
per_house_type = t1["NAME_HOUSING_TYPE"].value_counts()/len(t1)*100
per_house_type.plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("HOUSE_TYPE", labelpad=8)
plt.ylabel("PERCENTAGE", labelpad=8)
plt.title("HOUSE_TYPE_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

Observations¶

Payments issues observed for people with secondary/secondary special education
Payment issues obviously observed for Married people
Payment concerns are observed for people buying own house/apartment

## Income range for payment issues
per_income_cat_type = t1["AMT_INCOME_CAT"].value_counts()/len(t1)*100
per_income_cat_type.plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("INCOME_CATEGORY", labelpad=8)
plt.ylabel("PERCENTAGE", labelpad=8)
plt.title("INCOME_CATEGORY_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

Observations¶

Payments issues observed for people with low salary profiles around 30%

## Income range for payment issues
per_birth_cat_type = t1["DAYS_BIRTH_CAT"].value_counts()/len(t1)*100
per_birth_cat_type.plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("AGE_GROUP_CATEGORY", labelpad=8)
plt.ylabel("PERCENTAGE", labelpad=8)
plt.title("AGE_GROUP_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

Observations¶

Payments issues observed for middle aged people 60%

## NAME_TYPE_SUITE for payment issues
per_name_suite_type = t1["NAME_TYPE_SUITE"].value_counts()/len(t1)*100
per_name_suite_type.plot(kind='bar', figsize=(8, 6), rot=90)
plt.xlabel("TYPE_SUITE_CATEGORY", labelpad=8)
plt.ylabel("PERCENTAGE", labelpad=8)
plt.title("TYPE_SUITE_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

Observations¶

Payments issues observed for unaccompanied about 80% and family group with 10-12%

## ORGANIZATION_TYPE for payment issues
per_org_type = t1["ORGANIZATION_TYPE"].value_counts()/len(t1)*100
per_org_type.plot(kind='bar', figsize=(15, 12), rot=90)
plt.xlabel("ORGANIZATION_TYPE", labelpad=8)
plt.ylabel("PERCENTAGE", labelpad=8)
plt.title("ORGANIZATION_TYPE_DISTRIBUTION_WITH_PAYMENT_ISSUES", y=1.05);

Observations¶

Payments concerns observed for Business type-3(25-27%) and self employed(16-17%) people

### Instead of accessing columns, we will create a list and perform visualizations
obj_cols  = t1.select_dtypes("object").columns
cat_cols = t1.select_dtypes("category").columns
num_cols = t1.select_dtypes("float64", "int64").columns
### Moving them to separate lists
obj_lis  = list(obj_cols)
cat_lis  = list(cat_cols)
num_lis  = list(num_cols)

### Using for loop to plots the graph for analysis for object data types
for i in obj_lis:
    
    obj_counts_1=t1[i].value_counts(normalize=True)
    fig,(ax1,ax2,ax3) = plt.subplots(1,3,sharey=True,gridspec_kw={'width_ratios':[1,1, 0.001]},figsize=(15,3))
    print("Plot for ",i)
    char_1=sns.barplot(x=obj_counts_1.index, y=obj_counts_1, ax=ax1, palette="husl")
    char_1.set_xticklabels(char_1.get_xticklabels(minor=False), rotation=90, horizontalalignment='right')
    char_1.axes.set_title('Pay issues',fontsize= 12,fontweight= 7,color='blue')
    obj_counts_2=t0[i].value_counts(normalize=True)
    char_2=sns.barplot(x=obj_counts_2.index, y=obj_counts_2,ax=ax2, palette="Blues_d")
    char_2.set_xticklabels(char_2.get_xticklabels(), rotation=90, horizontalalignment='right')
    char_2.axes.set_title('No Pay issues',fontsize= 12,fontweight= 7,color='blue')
    plt.show();

Plot for  NAME_CONTRACT_TYPE

Plot for  CODE_GENDER

Plot for  FLAG_OWN_CAR

Plot for  FLAG_OWN_REALTY

Plot for  NAME_TYPE_SUITE

Plot for  NAME_INCOME_TYPE

Plot for  NAME_EDUCATION_TYPE

Plot for  NAME_FAMILY_STATUS

Plot for  NAME_HOUSING_TYPE

Plot for  OCCUPATION_TYPE

### Using for loop to plots the graph for analysis for category data types
for i in cat_lis:
    
    cat_counts_1=t1[i].value_counts(normalize=True)
    fig,(ax1,ax2,ax3) = plt.subplots(1,3,sharey=True,gridspec_kw={'width_ratios':[1,1, 0.001]},figsize=(15,3))
    print("Plot for ",i)
    char_1=sns.barplot(x=cat_counts_1.index, y=cat_counts_1, ax=ax1, palette="husl")
    char_1.set_xticklabels(char_1.get_xticklabels(minor=False), rotation=90, horizontalalignment='right')
    char_1.axes.set_title('Pay issues',fontsize= 12,fontweight= 7,color='blue')
    cat_counts_2=t0[i].value_counts(normalize=True)
    char_2=sns.barplot(x=cat_counts_1.index, y=cat_counts_2,ax=ax2, palette="Blues_d")
    char_2.set_xticklabels(char_2.get_xticklabels(), rotation=90, horizontalalignment='right')
    char_2.axes.set_title('No Pay issues',fontsize= 12,fontweight= 7,color='blue')
    plt.show();

Plot for  AMT_INCOME_CAT

Plot for  AMT_CREDIT_CAT

Plot for  DAYS_BIRTH_CAT

t1.NAME_CONTRACT_TYPE.value_counts(normalize = True), t0.NAME_CONTRACT_TYPE.value_counts(normalize = True)

(Cash loans         0.935388
 Revolving loans    0.064612
 Name: NAME_CONTRACT_TYPE, dtype: float64, Cash loans         0.902106
 Revolving loans    0.097894
 Name: NAME_CONTRACT_TYPE, dtype: float64)

Observation from categorical/object columns (Univariate analysis)¶

Cash Loans have high volume than revolving loans. No pay issues - 90% , Pay issues -93%
In both groups unaccompanied are applying loans for Target 0 and Target 1, This factor will not influence payment default.
Income type, 50% working people:Target 0 and 60% working people:Target 1.
In both target groups, loan applicants with Secondary Education/Special are more, numbers are higher in Target-1 group, the question is why more Secondary education applicants are more, need more details
Married applicants have applied for loans more in both groups (around 60%)
People having real estate and "House/apartment" have payment issues, this cannot be a factor to judge the same.
Labours, sales staff and drivers have much of the payment issues, labours are primary defaulters
Payments concerns observed for Business type-3(25-27%) and self employed(16-17%) people.
Females tend to have more payment issues relative to males.
People with no cars have more defaulters than people with cars.
Most of middle aged people are having payment issues.

### Using for loop to plots the graph for analysis for numerical data types
for i in num_lis:
    num_1=t1[i].value_counts()
    fig,(ax1,ax2,ax3) = plt.subplots(1,3,sharey=True,gridspec_kw={'width_ratios':[1,1, 0.01]},figsize=(14,4))
    print("Graph: ",i)
    char_1=sns.distplot(t1[i], ax=ax1,hist = False)
    char_1.axes.set_title('Pay issues',fontsize= 12,fontweight= 7,color='Green')
    
    num_2=t0[i].value_counts()
    char_2=sns.distplot(t0[i],ax=ax2,hist = False)
    char_2.axes.set_title('No Pay issues',fontsize= 12,fontweight= 7,color='Green')
    plt.show()

Graph:  AMT_INCOME_TOTAL

Graph:  AMT_CREDIT

Graph:  AMT_ANNUITY

Graph:  AMT_GOODS_PRICE

Graph:  REGION_POPULATION_RELATIVE

Graph:  DAYS_BIRTH

Graph:  DAYS_EMPLOYED

Graph:  DAYS_REGISTRATION

Graph:  DAYS_ID_PUBLISH

Graph:  OWN_CAR_AGE

for i in num_lis:
    sns.distplot(t0[i], hist = False, label = 'No Pay issues')
    sns.distplot(t1[i], hist = False, label = 'Pay issues')
    plt.show();

Observations¶

Credit amount is lower for Target 1, which is a good sign as lesser default loss to the company.
Youger people are defaulters more
External sources show higher frequency of scores for target-0 group
observation of client's social surroundings (OBS_30_CNT_SOCIAL_CIRCLE, DEF_30_CNT_SOCIAL_CIRCLE, OBS_60_CNT_SOCIAL_CIRCLE, DEF_60_CNT_SOCIAL_CIRCLE)is more prominent for Target-1 group
Days employed doesn't have proper information based on plot data
Number of enquiries to Credit Bureau is observed for people with payment issues(Target-1) as they have applied at several companies

t1.DAYS_LAST_PHONE_CHANGE =  abs(t1["DAYS_LAST_PHONE_CHANGE"])
t0.DAYS_LAST_PHONE_CHANGE =  abs(t0["DAYS_LAST_PHONE_CHANGE"])

plt.figure(figsize=(8,6))

grap=sns.countplot(x='AMT_CREDIT_CAT', data=app1, hue="TARGET", palette="Paired")
grap.set_xticklabels(grap.get_xticklabels(), rotation=90, horizontalalignment='right')
grap.axes.set_title('AMT_CREDIT_CAT',fontsize= 10,fontweight= 5,color='Green')
plt.show();

t1.DAYS_LAST_PHONE_CHANGE.describe()

count    24825.000000
mean       808.796818
std        758.090713
min          0.000000
25%        194.000000
50%        594.000000
75%       1301.000000
max       4070.000000
Name: DAYS_LAST_PHONE_CHANGE, dtype: float64

t1.DAYS_LAST_PHONE_CHANGE.plot(kind="hist")

<matplotlib.axes._subplots.AxesSubplot at 0x128647ea7b8>

col_reg = [c for c in t1.columns if c.startswith('REG')]
col_reg

['REGION_POPULATION_RELATIVE',
 'REGION_RATING_CLIENT',
 'REGION_RATING_CLIENT_W_CITY',
 'REG_REGION_NOT_LIVE_REGION',
 'REG_REGION_NOT_WORK_REGION',
 'REG_CITY_NOT_LIVE_CITY',
 'REG_CITY_NOT_WORK_CITY']

for i in col_reg:
    sns.distplot(t0[i], hist = False, label = 'No Pay issues')
    sns.distplot(t1[i], hist = False, label = 'Pay issues')
    plt.show();

Observations¶

People with payment issues (target-1) have client's permanent address does not match well with contact and work address lot of changes have been made based on data.
There is tendency of people with payment issues living away from city limits probably the cost of living.

Bi-variate analysis (Checking on two variables at a time)¶

## Saving the two group of people in separate files for reference
t1.to_csv('t1_app.csv', index=False)
t0.to_csv('t0_app.csv', index=False)

t1 = pd.read_csv("t1_app.csv")
t0 = pd.read_csv("t0_app.csv")

t1.columns

Index(['SK_ID_CURR', 'TARGET', 'NAME_CONTRACT_TYPE', 'CODE_GENDER',
       'FLAG_OWN_CAR', 'FLAG_OWN_REALTY', 'CNT_CHILDREN', 'AMT_INCOME_TOTAL',
       'AMT_CREDIT', 'AMT_ANNUITY', 'AMT_GOODS_PRICE', 'NAME_TYPE_SUITE',
       'NAME_INCOME_TYPE', 'NAME_EDUCATION_TYPE', 'NAME_FAMILY_STATUS',
       'NAME_HOUSING_TYPE', 'REGION_POPULATION_RELATIVE', 'DAYS_BIRTH',
       'DAYS_EMPLOYED', 'DAYS_REGISTRATION', 'DAYS_ID_PUBLISH', 'OWN_CAR_AGE',
       'FLAG_MOBIL', 'FLAG_EMP_PHONE', 'FLAG_WORK_PHONE', 'FLAG_CONT_MOBILE',
       'FLAG_PHONE', 'FLAG_EMAIL', 'OCCUPATION_TYPE', 'CNT_FAM_MEMBERS',
       'REGION_RATING_CLIENT', 'REGION_RATING_CLIENT_W_CITY',
       'WEEKDAY_APPR_PROCESS_START', 'HOUR_APPR_PROCESS_START',
       'REG_REGION_NOT_LIVE_REGION', 'REG_REGION_NOT_WORK_REGION',
       'LIVE_REGION_NOT_WORK_REGION', 'REG_CITY_NOT_LIVE_CITY',
       'REG_CITY_NOT_WORK_CITY', 'LIVE_CITY_NOT_WORK_CITY',
       'ORGANIZATION_TYPE', 'EXT_SOURCE_2', 'EXT_SOURCE_3',
       'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'FLAG_DOCUMENT_2', 'FLAG_DOCUMENT_3',
       'FLAG_DOCUMENT_4', 'FLAG_DOCUMENT_5', 'FLAG_DOCUMENT_6',
       'FLAG_DOCUMENT_7', 'FLAG_DOCUMENT_8', 'FLAG_DOCUMENT_9',
       'FLAG_DOCUMENT_10', 'FLAG_DOCUMENT_11', 'FLAG_DOCUMENT_12',
       'FLAG_DOCUMENT_13', 'FLAG_DOCUMENT_14', 'FLAG_DOCUMENT_15',
       'FLAG_DOCUMENT_16', 'FLAG_DOCUMENT_17', 'FLAG_DOCUMENT_18',
       'FLAG_DOCUMENT_19', 'FLAG_DOCUMENT_20', 'FLAG_DOCUMENT_21',
       'AMT_REQ_CREDIT_BUREAU_HOUR', 'AMT_REQ_CREDIT_BUREAU_DAY',
       'AMT_REQ_CREDIT_BUREAU_WEEK', 'AMT_REQ_CREDIT_BUREAU_MON',
       'AMT_REQ_CREDIT_BUREAU_QRT', 'AMT_REQ_CREDIT_BUREAU_YEAR',
       'AMT_INCOME_CAT', 'AMT_CREDIT_CAT', 'DAYS_BIRTH_CAT'],
      dtype='object')

plt.figure(figsize = (18,8))
plt.subplot(1,2,1)
plt.title("Income & Amt Credit group for Target 1")
sns.boxplot(x = 'AMT_INCOME_CAT', y = 'AMT_CREDIT', data =t1, showfliers=False, palette="cubehelix")

plt.subplot(1,2,2)
plt.title("Income & Amt Credit group for Target 0")
sns.boxplot(x = 'AMT_INCOME_CAT', y = 'AMT_CREDIT', data = t0, showfliers=False, palette="cubehelix")
plt.show();

plt.figure(figsize = (20,4))
plt.subplot(1,2,1)
plt.title("Income_Group and Payment Diffculty")
sns.countplot('AMT_INCOME_CAT', hue = 'TARGET', data =app1)
plt.show()

app1.columns

Index(['SK_ID_CURR', 'TARGET', 'NAME_CONTRACT_TYPE', 'CODE_GENDER',
       'FLAG_OWN_CAR', 'FLAG_OWN_REALTY', 'CNT_CHILDREN', 'AMT_INCOME_TOTAL',
       'AMT_CREDIT', 'AMT_ANNUITY', 'AMT_GOODS_PRICE', 'NAME_TYPE_SUITE',
       'NAME_INCOME_TYPE', 'NAME_EDUCATION_TYPE', 'NAME_FAMILY_STATUS',
       'NAME_HOUSING_TYPE', 'REGION_POPULATION_RELATIVE', 'DAYS_BIRTH',
       'DAYS_EMPLOYED', 'DAYS_REGISTRATION', 'DAYS_ID_PUBLISH', 'OWN_CAR_AGE',
       'FLAG_MOBIL', 'FLAG_EMP_PHONE', 'FLAG_WORK_PHONE', 'FLAG_CONT_MOBILE',
       'FLAG_PHONE', 'FLAG_EMAIL', 'OCCUPATION_TYPE', 'CNT_FAM_MEMBERS',
       'REGION_RATING_CLIENT', 'REGION_RATING_CLIENT_W_CITY',
       'WEEKDAY_APPR_PROCESS_START', 'HOUR_APPR_PROCESS_START',
       'REG_REGION_NOT_LIVE_REGION', 'REG_REGION_NOT_WORK_REGION',
       'LIVE_REGION_NOT_WORK_REGION', 'REG_CITY_NOT_LIVE_CITY',
       'REG_CITY_NOT_WORK_CITY', 'LIVE_CITY_NOT_WORK_CITY',
       'ORGANIZATION_TYPE', 'EXT_SOURCE_2', 'EXT_SOURCE_3',
       'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'FLAG_DOCUMENT_2', 'FLAG_DOCUMENT_3',
       'FLAG_DOCUMENT_4', 'FLAG_DOCUMENT_5', 'FLAG_DOCUMENT_6',
       'FLAG_DOCUMENT_7', 'FLAG_DOCUMENT_8', 'FLAG_DOCUMENT_9',
       'FLAG_DOCUMENT_10', 'FLAG_DOCUMENT_11', 'FLAG_DOCUMENT_12',
       'FLAG_DOCUMENT_13', 'FLAG_DOCUMENT_14', 'FLAG_DOCUMENT_15',
       'FLAG_DOCUMENT_16', 'FLAG_DOCUMENT_17', 'FLAG_DOCUMENT_18',
       'FLAG_DOCUMENT_19', 'FLAG_DOCUMENT_20', 'FLAG_DOCUMENT_21',
       'AMT_REQ_CREDIT_BUREAU_HOUR', 'AMT_REQ_CREDIT_BUREAU_DAY',
       'AMT_REQ_CREDIT_BUREAU_WEEK', 'AMT_REQ_CREDIT_BUREAU_MON',
       'AMT_REQ_CREDIT_BUREAU_QRT', 'AMT_REQ_CREDIT_BUREAU_YEAR',
       'AMT_INCOME_CAT', 'AMT_CREDIT_CAT', 'DAYS_BIRTH_CAT'],
      dtype='object')

piv1 = pd.pivot_table(data = app1, index = "AMT_INCOME_CAT", columns = "TARGET",values='AMT_CREDIT', aggfunc="mean")
piv1

piv2 = pd.pivot_table(data = app1, index = "DAYS_BIRTH_CAT", columns = "TARGET",values='AMT_CREDIT', aggfunc="mean")
piv2

piv3 = pd.pivot_table(data = app1, index = "NAME_INCOME_TYPE", columns = "CODE_GENDER",values='AMT_CREDIT', aggfunc="mean")
piv3

piv4 = pd.pivot_table(data = t1, index = "NAME_INCOME_TYPE", columns = "CODE_GENDER",values='AMT_CREDIT', aggfunc="mean")
piv4

piv5 = pd.pivot_table(data = app1, index = "NAME_EDUCATION_TYPE", columns = "TARGET",values='AMT_CREDIT', aggfunc="mean")
piv5

piv6 = pd.pivot_table(data = t1, index = "NAME_EDUCATION_TYPE", columns = "TARGET",values='AMT_CREDIT', aggfunc="mean")
piv6

### Checking on contract type and target groups with gender
plt.figure(figsize = (20,4))
plt.subplot(1,2,1)
plt.title("CONTRACT_TYPE and TARGET : Males")
sns.countplot('NAME_CONTRACT_TYPE', hue = "TARGET", data=app1[(app1['CODE_GENDER']=="M")])

plt.subplot(1,2,2)
plt.title("CONTRACT_TYPE and TARGET : Females")
sns.countplot('NAME_CONTRACT_TYPE', hue = "TARGET", data=app1[(app1['CODE_GENDER']=="F")])

plt.show();

### Checking on Income type and income groups with gender
plt.figure(figsize = (24,8))
###
plt.subplot(1,2,1)
plt.title("Income group and type for target-1")
sns.countplot('NAME_INCOME_TYPE', hue = 'AMT_INCOME_CAT', data =t1, palette="rocket")
plt.xticks(rotation=20, fontsize= 10,fontweight= 2)
####
plt.subplot(1,2,2)
plt.title("Income group and type for target-0")
sns.countplot('NAME_INCOME_TYPE', hue = 'AMT_INCOME_CAT', data =t0, palette="rocket")
plt.xticks(rotation=20, fontsize= 10,fontweight= 2)

plt.show();

### Checking on Family status and Age groups with Target
plt.figure(figsize = (24,8))
###
plt.subplot(1,2,1)
plt.title("Age group and family status for target-1")
sns.countplot('NAME_FAMILY_STATUS', hue = 'DAYS_BIRTH_CAT', data =t1, palette="plasma_r")
plt.xticks(rotation=20, fontsize= 10,fontweight= 2)
####
plt.subplot(1,2,2)
plt.title("Age group and family status for target-0")
sns.countplot('NAME_FAMILY_STATUS', hue = 'DAYS_BIRTH_CAT', data =t0, palette="plasma_r")
plt.xticks(rotation=20, fontsize= 10,fontweight= 2)

plt.show();

Observations¶

Maximum number of loans is offered/credited to people with medium and high income categories.
There are also chances that people with medium salary could have issues or repaying, some guideline needed here
Middle aged people and Seniors are offered high loan credits relative Teenagers/Young people
If we observe the box plot for Target-1 , we see that median of loans credited with Medium salaried and High salaried in close range, this might have payment issues, requires company to focus here.
In the Income type categories, we observe higher credits of loans to Males compared to females.However, In the payment issues group(target-1) females have high credits expecially women under maternity leave.
In payment issues group, people with Academic degree are paid high loan credits, Bank/company has to rethink on some guidelines here.
In contract type , cash loans, Males are having payment issues more than females.
The income group with 'low' salaries and 'High' salaries working professionals are having payment issues.
Married applicants are facing payment issues.

### Combining category and object lists
cat_lis = cat_lis +  obj_lis
cat_lis

['AMT_INCOME_CAT',
 'AMT_CREDIT_CAT',
 'DAYS_BIRTH_CAT',
 'NAME_CONTRACT_TYPE',
 'CODE_GENDER',
 'FLAG_OWN_CAR',
 'FLAG_OWN_REALTY',
 'NAME_TYPE_SUITE',
 'NAME_INCOME_TYPE',
 'NAME_EDUCATION_TYPE',
 'NAME_FAMILY_STATUS',
 'NAME_HOUSING_TYPE',
 'OCCUPATION_TYPE',
 'WEEKDAY_APPR_PROCESS_START',
 'ORGANIZATION_TYPE']

num_lis

['AMT_INCOME_TOTAL',
 'AMT_CREDIT',
 'AMT_ANNUITY',
 'AMT_GOODS_PRICE',
 'REGION_POPULATION_RELATIVE',
 'DAYS_BIRTH',
 'DAYS_EMPLOYED',
 'DAYS_REGISTRATION',
 'DAYS_ID_PUBLISH',
 'OWN_CAR_AGE',
 'CNT_FAM_MEMBERS',
 'EXT_SOURCE_2',
 'EXT_SOURCE_3',
 'OBS_30_CNT_SOCIAL_CIRCLE',
 'DEF_30_CNT_SOCIAL_CIRCLE',
 'OBS_60_CNT_SOCIAL_CIRCLE',
 'DEF_60_CNT_SOCIAL_CIRCLE',
 'DAYS_LAST_PHONE_CHANGE',
 'AMT_REQ_CREDIT_BUREAU_HOUR',
 'AMT_REQ_CREDIT_BUREAU_DAY',
 'AMT_REQ_CREDIT_BUREAU_WEEK',
 'AMT_REQ_CREDIT_BUREAU_MON',
 'AMT_REQ_CREDIT_BUREAU_QRT',
 'AMT_REQ_CREDIT_BUREAU_YEAR']

num_lis_int = ['AMT_INCOME_TOTAL','AMT_CREDIT','AMT_ANNUITY','AMT_GOODS_PRICE','DAYS_BIRTH']

#Pairplot for target-1, payment issues
pair1 = t1[['AMT_INCOME_TOTAL','AMT_CREDIT','AMT_ANNUITY','AMT_GOODS_PRICE','DAYS_BIRTH']].fillna(0)
sns.pairplot(pair1)

plt.show();

#Pairplot for target-0, No payment issues
pair0 = t0[['AMT_INCOME_TOTAL','AMT_CREDIT','AMT_ANNUITY','AMT_GOODS_PRICE','DAYS_BIRTH']].fillna(0)
sns.pairplot(pair0)

plt.show();

To identify the top 10 correlations, we will focus on factors that had some relationship merely or more and will add those variables to a new list some variables are evident from pair plots and others will be added based on observations

corr_var_num = ['AMT_INCOME_TOTAL',
 'AMT_CREDIT',
 'AMT_ANNUITY',
 'AMT_GOODS_PRICE',
 'REGION_POPULATION_RELATIVE',
 'DAYS_BIRTH',
 'DAYS_EMPLOYED',
 'DAYS_REGISTRATION',
 'DAYS_ID_PUBLISH']

## Generating heat map for the multi-variables of interest based on observations and analysis
corr_num_1 = t1[corr_var_num].corr(method = 'pearson')
plt.figure(figsize=(20,15))
sns.heatmap(corr_num_1, cmap='viridis')
plt.show();

# Performing correlation for Target-1 group
corr_df1 = corr_num_1.where(np.triu(np.ones(corr_num_1.shape),k=1).astype(np.bool)).unstack().reset_index()
corr_df1.columns=['Column1','Column2','Correlation']
corr_df1.dropna(subset = ['Correlation'], inplace = True)
corr_df1 = corr_df1.sort_values(by=['Correlation'], ascending=False)

corr_df1.head(10)

	SK_ID_CURR	TARGET	CNT_CHILDREN	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	AMT_GOODS_PRICE	REGION_POPULATION_RELATIVE	DAYS_BIRTH	DAYS_EMPLOYED	DAYS_REGISTRATION	DAYS_ID_PUBLISH	OWN_CAR_AGE	FLAG_MOBIL	FLAG_EMP_PHONE	FLAG_WORK_PHONE	FLAG_CONT_MOBILE	FLAG_PHONE	FLAG_EMAIL	CNT_FAM_MEMBERS	REGION_RATING_CLIENT	REGION_RATING_CLIENT_W_CITY	HOUR_APPR_PROCESS_START	REG_REGION_NOT_LIVE_REGION	REG_REGION_NOT_WORK_REGION	LIVE_REGION_NOT_WORK_REGION	REG_CITY_NOT_LIVE_CITY	REG_CITY_NOT_WORK_CITY	LIVE_CITY_NOT_WORK_CITY	EXT_SOURCE_1	EXT_SOURCE_2	EXT_SOURCE_3	APARTMENTS_AVG	BASEMENTAREA_AVG	YEARS_BEGINEXPLUATATION_AVG	YEARS_BUILD_AVG	COMMONAREA_AVG	ELEVATORS_AVG	ENTRANCES_AVG	FLOORSMAX_AVG	FLOORSMIN_AVG	LANDAREA_AVG	LIVINGAPARTMENTS_AVG	LIVINGAREA_AVG	NONLIVINGAPARTMENTS_AVG	NONLIVINGAREA_AVG	APARTMENTS_MODE	BASEMENTAREA_MODE	YEARS_BEGINEXPLUATATION_MODE	YEARS_BUILD_MODE	COMMONAREA_MODE	ELEVATORS_MODE	ENTRANCES_MODE	FLOORSMAX_MODE	FLOORSMIN_MODE	LANDAREA_MODE	LIVINGAPARTMENTS_MODE	LIVINGAREA_MODE	NONLIVINGAPARTMENTS_MODE	NONLIVINGAREA_MODE	APARTMENTS_MEDI	BASEMENTAREA_MEDI	YEARS_BEGINEXPLUATATION_MEDI	YEARS_BUILD_MEDI	COMMONAREA_MEDI	ELEVATORS_MEDI	ENTRANCES_MEDI	FLOORSMAX_MEDI	FLOORSMIN_MEDI	LANDAREA_MEDI	LIVINGAPARTMENTS_MEDI	LIVINGAREA_MEDI	NONLIVINGAPARTMENTS_MEDI	NONLIVINGAREA_MEDI	TOTALAREA_MODE	OBS_30_CNT_SOCIAL_CIRCLE	DEF_30_CNT_SOCIAL_CIRCLE	OBS_60_CNT_SOCIAL_CIRCLE	DEF_60_CNT_SOCIAL_CIRCLE	DAYS_LAST_PHONE_CHANGE	FLAG_DOCUMENT_2	FLAG_DOCUMENT_3	FLAG_DOCUMENT_4	FLAG_DOCUMENT_5	FLAG_DOCUMENT_6	FLAG_DOCUMENT_7	FLAG_DOCUMENT_8	FLAG_DOCUMENT_9	FLAG_DOCUMENT_10	FLAG_DOCUMENT_11	FLAG_DOCUMENT_12	FLAG_DOCUMENT_13	FLAG_DOCUMENT_14	FLAG_DOCUMENT_15	FLAG_DOCUMENT_16	FLAG_DOCUMENT_17	FLAG_DOCUMENT_18	FLAG_DOCUMENT_19	FLAG_DOCUMENT_20	FLAG_DOCUMENT_21	AMT_REQ_CREDIT_BUREAU_HOUR	AMT_REQ_CREDIT_BUREAU_DAY	AMT_REQ_CREDIT_BUREAU_WEEK	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
count	307511.000000	307511.000000	307511.000000	3.075110e+05	3.075110e+05	307499.000000	3.072330e+05	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	104582.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307509.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	134133.000000	3.068510e+05	246546.000000	151450.00000	127568.000000	157504.000000	103023.000000	92646.000000	143620.000000	152683.000000	154491.000000	98869.000000	124921.000000	97312.000000	153161.000000	93997.000000	137829.000000	151450.000000	127568.000000	157504.000000	103023.000000	92646.000000	143620.000000	152683.000000	154491.000000	98869.000000	124921.000000	97312.000000	153161.000000	93997.000000	137829.000000	151450.000000	127568.000000	157504.000000	103023.000000	92646.000000	143620.000000	152683.000000	154491.000000	98869.000000	124921.000000	97312.000000	153161.000000	93997.000000	137829.000000	159080.000000	306490.000000	306490.000000	306490.000000	306490.000000	307510.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.00000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	307511.000000	265992.000000	265992.000000	265992.000000	265992.000000	265992.000000	265992.000000
mean	278180.518577	0.080729	0.417052	1.687979e+05	5.990260e+05	27108.573909	5.383962e+05	0.020868	-16036.995067	63815.045904	-4986.120328	-2994.202373	12.061091	0.999997	0.819889	0.199368	0.998133	0.281066	0.056720	2.152665	2.052463	2.031521	12.063419	0.015144	0.050769	0.040659	0.078173	0.230454	0.179555	0.502130	5.143927e-01	0.510853	0.11744	0.088442	0.977735	0.752471	0.044621	0.078942	0.149725	0.226282	0.231894	0.066333	0.100775	0.107399	0.008809	0.028358	0.114231	0.087543	0.977065	0.759637	0.042553	0.074490	0.145193	0.222315	0.228058	0.064958	0.105645	0.105975	0.008076	0.027022	0.117850	0.087955	0.977752	0.755746	0.044595	0.078078	0.149213	0.225897	0.231625	0.067169	0.101954	0.108607	0.008651	0.028236	0.102547	1.422245	0.143421	1.405292	0.100049	-962.858788	0.000042	0.710023	0.000081	0.015115	0.088055	0.000192	0.081376	0.003896	0.000023	0.003912	0.000007	0.003525	0.002936	0.00121	0.009928	0.000267	0.008130	0.000595	0.000507	0.000335	0.006402	0.007000	0.034362	0.267395	0.265474	1.899974
std	102790.175348	0.272419	0.722121	2.371231e+05	4.024908e+05	14493.737315	3.694465e+05	0.013831	4363.988632	141275.766519	3522.886321	1509.450419	11.944812	0.001803	0.384280	0.399526	0.043164	0.449521	0.231307	0.910682	0.509034	0.502737	3.265832	0.122126	0.219526	0.197499	0.268444	0.421124	0.383817	0.211062	1.910602e-01	0.194844	0.10824	0.082438	0.059223	0.113280	0.076036	0.134576	0.100049	0.144641	0.161380	0.081184	0.092576	0.110565	0.047732	0.069523	0.107936	0.084307	0.064575	0.110111	0.074445	0.132256	0.100977	0.143709	0.161160	0.081750	0.097880	0.111845	0.046276	0.070254	0.109076	0.082179	0.059897	0.112066	0.076144	0.134467	0.100368	0.145067	0.161934	0.082167	0.093642	0.112260	0.047415	0.070166	0.107462	2.400989	0.446698	2.379803	0.362291	826.808487	0.006502	0.453752	0.009016	0.122010	0.283376	0.013850	0.273412	0.062295	0.004771	0.062424	0.002550	0.059268	0.054110	0.03476	0.099144	0.016327	0.089798	0.024387	0.022518	0.018299	0.083849	0.110757	0.204685	0.916002	0.794056	1.869295
min	100002.000000	0.000000	0.000000	2.565000e+04	4.500000e+04	1615.500000	4.050000e+04	0.000290	-25229.000000	-17912.000000	-24672.000000	-7197.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	1.000000	1.000000	1.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.014568	8.173617e-08	0.000527	0.00000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	-4292.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.00000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000
25%	189145.500000	0.000000	0.000000	1.125000e+05	2.700000e+05	16524.000000	2.385000e+05	0.010006	-19682.000000	-2760.000000	-7479.500000	-4299.000000	5.000000	1.000000	1.000000	0.000000	1.000000	0.000000	0.000000	2.000000	2.000000	2.000000	10.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.334007	3.924574e-01	0.370650	0.05770	0.044200	0.976700	0.687200	0.007800	0.000000	0.069000	0.166700	0.083300	0.018700	0.050400	0.045300	0.000000	0.000000	0.052500	0.040700	0.976700	0.699400	0.007200	0.000000	0.069000	0.166700	0.083300	0.016600	0.054200	0.042700	0.000000	0.000000	0.058300	0.043700	0.976700	0.691400	0.007900	0.000000	0.069000	0.166700	0.083300	0.018700	0.051300	0.045700	0.000000	0.000000	0.041200	0.000000	0.000000	0.000000	0.000000	-1570.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.00000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000
50%	278202.000000	0.000000	0.000000	1.471500e+05	5.135310e+05	24903.000000	4.500000e+05	0.018850	-15750.000000	-1213.000000	-4504.000000	-3254.000000	9.000000	1.000000	1.000000	0.000000	1.000000	0.000000	0.000000	2.000000	2.000000	2.000000	12.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.505998	5.659614e-01	0.535276	0.08760	0.076300	0.981600	0.755200	0.021100	0.000000	0.137900	0.166700	0.208300	0.048100	0.075600	0.074500	0.000000	0.003600	0.084000	0.074600	0.981600	0.764800	0.019000	0.000000	0.137900	0.166700	0.208300	0.045800	0.077100	0.073100	0.000000	0.001100	0.086400	0.075800	0.981600	0.758500	0.020800	0.000000	0.137900	0.166700	0.208300	0.048700	0.076100	0.074900	0.000000	0.003100	0.068800	0.000000	0.000000	0.000000	0.000000	-757.000000	0.000000	1.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.00000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	1.000000
75%	367142.500000	0.000000	1.000000	2.025000e+05	8.086500e+05	34596.000000	6.795000e+05	0.028663	-12413.000000	-289.000000	-2010.000000	-1720.000000	15.000000	1.000000	1.000000	0.000000	1.000000	1.000000	0.000000	3.000000	2.000000	2.000000	14.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.675053	6.636171e-01	0.669057	0.14850	0.112200	0.986600	0.823200	0.051500	0.120000	0.206900	0.333300	0.375000	0.085600	0.121000	0.129900	0.003900	0.027700	0.143900	0.112400	0.986600	0.823600	0.049000	0.120800	0.206900	0.333300	0.375000	0.084100	0.131300	0.125200	0.003900	0.023100	0.148900	0.111600	0.986600	0.825600	0.051300	0.120000	0.206900	0.333300	0.375000	0.086800	0.123100	0.130300	0.003900	0.026600	0.127600	2.000000	0.000000	2.000000	0.000000	-274.000000	0.000000	1.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.00000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	3.000000
max	456255.000000	1.000000	19.000000	1.170000e+08	4.050000e+06	258025.500000	4.050000e+06	0.072508	-7489.000000	365243.000000	0.000000	0.000000	91.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	20.000000	3.000000	3.000000	23.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	0.962693	8.549997e-01	0.896010	1.00000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	348.000000	34.000000	344.000000	24.000000	0.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.00000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	4.000000	9.000000	8.000000	27.000000	261.000000	25.000000

	DAYS_BIRTH	DAYS_EMPLOYED	DAYS_REGISTRATION	DAYS_ID_PUBLISH
count	307508.000000	307508.000000	307508.000000	307508.000000
mean	16037.041560	67725.397378	4986.137584	2994.208382
std	4363.980429	139444.274200	3522.896819	1509.449226
min	7489.000000	0.000000	0.000000	0.000000
25%	12413.000000	933.000000	2010.000000	1720.000000
50%	15750.000000	2219.000000	4504.000000	3254.000000
75%	19682.000000	5707.000000	7480.000000	4299.000000
max	25229.000000	365243.000000	24672.000000	7197.000000

TARGET	0	1
AMT_INCOME_CAT
VLOW	385910.055205	401071.078852
LOW	515987.800804	496230.923218
MEDIOCRE	604593.018070	574222.523384
HIGH	699630.470905	643297.927515
VHIGH	890191.216722	786021.833496

TARGET	0	1
DAYS_BIRTH_CAT
TEENAGE	414805.759989	415780.998329
MIDDLE-AGE	598925.529508	543322.883919
SENIORS	622079.330806	607769.362645

CODE_GENDER	F	M	XNA
NAME_INCOME_TYPE
Businessman	1.125000e+06	1.272857e+06	NaN
Commercial associate	6.651503e+05	6.777461e+05	540000.0
Maternity leave	7.458750e+05	7.650000e+05	NaN
Pensioner	5.363186e+05	5.704503e+05	NaN
State servant	6.624039e+05	6.899287e+05	NaN
Student	3.725563e+05	5.987528e+05	NaN
Unemployed	7.403824e+05	8.460000e+05	NaN
Working	5.744283e+05	5.810590e+05	352500.0

	SK_ID_CURR	TARGET	NAME_CONTRACT_TYPE	CODE_GENDER	FLAG_OWN_CAR	FLAG_OWN_REALTY	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	AMT_GOODS_PRICE	NAME_TYPE_SUITE	NAME_INCOME_TYPE	NAME_EDUCATION_TYPE	NAME_FAMILY_STATUS	NAME_HOUSING_TYPE	REGION_POPULATION_RELATIVE	DAYS_BIRTH	DAYS_EMPLOYED	DAYS_REGISTRATION	DAYS_ID_PUBLISH	OWN_CAR_AGE	FLAG_MOBIL	FLAG_EMP_PHONE	FLAG_WORK_PHONE	FLAG_CONT_MOBILE	FLAG_PHONE	OCCUPATION_TYPE	CNT_FAM_MEMBERS	REGION_RATING_CLIENT	REGION_RATING_CLIENT_W_CITY	WEEKDAY_APPR_PROCESS_START	HOUR_APPR_PROCESS_START	REG_CITY_NOT_WORK_CITY	LIVE_CITY_NOT_WORK_CITY	ORGANIZATION_TYPE	EXT_SOURCE_1	EXT_SOURCE_2	EXT_SOURCE_3	APARTMENTS_AVG	BASEMENTAREA_AVG	YEARS_BEGINEXPLUATATION_AVG	YEARS_BUILD_AVG	COMMONAREA_AVG	ELEVATORS_AVG	ENTRANCES_AVG	FLOORSMAX_AVG	FLOORSMIN_AVG	LANDAREA_AVG	LIVINGAPARTMENTS_AVG	LIVINGAREA_AVG	NONLIVINGAPARTMENTS_AVG	NONLIVINGAREA_AVG	APARTMENTS_MODE	BASEMENTAREA_MODE	YEARS_BEGINEXPLUATATION_MODE	YEARS_BUILD_MODE	COMMONAREA_MODE	ELEVATORS_MODE	ENTRANCES_MODE	FLOORSMAX_MODE	FLOORSMIN_MODE	LANDAREA_MODE	LIVINGAPARTMENTS_MODE	LIVINGAREA_MODE	NONLIVINGAPARTMENTS_MODE	NONLIVINGAREA_MODE	APARTMENTS_MEDI	BASEMENTAREA_MEDI	YEARS_BEGINEXPLUATATION_MEDI	YEARS_BUILD_MEDI	COMMONAREA_MEDI	ELEVATORS_MEDI	ENTRANCES_MEDI	FLOORSMAX_MEDI	FLOORSMIN_MEDI	LANDAREA_MEDI	LIVINGAPARTMENTS_MEDI	LIVINGAREA_MEDI	NONLIVINGAPARTMENTS_MEDI	NONLIVINGAREA_MEDI	FONDKAPREMONT_MODE	HOUSETYPE_MODE	TOTALAREA_MODE	WALLSMATERIAL_MODE	EMERGENCYSTATE_MODE	OBS_30_CNT_SOCIAL_CIRCLE	DEF_30_CNT_SOCIAL_CIRCLE	OBS_60_CNT_SOCIAL_CIRCLE	DEF_60_CNT_SOCIAL_CIRCLE	DAYS_LAST_PHONE_CHANGE	FLAG_DOCUMENT_3	FLAG_DOCUMENT_8	AMT_REQ_CREDIT_BUREAU_HOUR	AMT_REQ_CREDIT_BUREAU_DAY	AMT_REQ_CREDIT_BUREAU_WEEK	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
0	100002	1	Cash loans	M	N	Y	202500.0	406597.5	24700.5	351000.0	Unaccompanied	Working	Secondary / secondary special	Single / not married	House / apartment	0.018801	-9461	-637	-3648.0	-2120	NaN	1	1	0	1	1	Laborers	1.0	2	2	WEDNESDAY	10	0	0	Business Entity Type 3	0.083037	0.262949	0.139376	0.0247	0.0369	0.9722	0.6192	0.0143	0.00	0.0690	0.0833	0.1250	0.0369	0.0202	0.0190	0.0000	0.0000	0.0252	0.0383	0.9722	0.6341	0.0144	0.0000	0.0690	0.0833	0.1250	0.0377	0.022	0.0198	0.0	0.0	0.0250	0.0369	0.9722	0.6243	0.0144	0.00	0.0690	0.0833	0.1250	0.0375	0.0205	0.0193	0.0000	0.00	reg oper account	block of flats	0.0149	Stone, brick	No	2.0	2.0	2.0	2.0	-1134.0	1	0	0.0	0.0	0.0	0.0	0.0	1.0
1	100003	0	Cash loans	F	N	N	270000.0	1293502.5	35698.5	1129500.0	Family	State servant	Higher education	Married	House / apartment	0.003541	-16765	-1188	-1186.0	-291	NaN	1	1	0	1	1	Core staff	2.0	1	1	MONDAY	11	0	0	School	0.311267	0.622246	NaN	0.0959	0.0529	0.9851	0.7960	0.0605	0.08	0.0345	0.2917	0.3333	0.0130	0.0773	0.0549	0.0039	0.0098	0.0924	0.0538	0.9851	0.8040	0.0497	0.0806	0.0345	0.2917	0.3333	0.0128	0.079	0.0554	0.0	0.0	0.0968	0.0529	0.9851	0.7987	0.0608	0.08	0.0345	0.2917	0.3333	0.0132	0.0787	0.0558	0.0039	0.01	reg oper account	block of flats	0.0714	Block	No	1.0	0.0	1.0	0.0	-828.0	1	0	0.0	0.0	0.0	0.0	0.0	0.0
2	100004	0	Revolving loans	M	Y	Y	67500.0	135000.0	6750.0	135000.0	Unaccompanied	Working	Secondary / secondary special	Single / not married	House / apartment	0.010032	-19046	-225	-4260.0	-2531	26.0	1	1	1	1	1	Laborers	1.0	2	2	MONDAY	9	0	0	Government	NaN	0.555912	0.729567	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	0.0	0.0	0.0	0.0	-815.0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
3	100006	0	Cash loans	F	N	Y	135000.0	312682.5	29686.5	297000.0	Unaccompanied	Working	Secondary / secondary special	Civil marriage	House / apartment	0.008019	-19005	-3039	-9833.0	-2437	NaN	1	1	0	1	0	Laborers	2.0	2	2	WEDNESDAY	17	0	0	Business Entity Type 3	NaN	0.650442	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	2.0	0.0	2.0	0.0	-617.0	1	0	NaN	NaN	NaN	NaN	NaN	NaN
4	100007	0	Cash loans	M	N	Y	121500.0	513000.0	21865.5	513000.0	Unaccompanied	Working	Secondary / secondary special	Single / not married	House / apartment	0.028663	-19932	-3038	-4311.0	-3458	NaN	1	1	0	1	0	Core staff	1.0	2	2	THURSDAY	11	1	1	Religion	NaN	0.322738	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	0.0	0.0	0.0	0.0	-1106.0	0	1	0.0	0.0	0.0	0.0	0.0	0.0

	SK_ID_CURR	NAME_CONTRACT_TYPE	CODE_GENDER	FLAG_OWN_CAR	FLAG_OWN_REALTY	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	AMT_GOODS_PRICE	NAME_TYPE_SUITE	NAME_INCOME_TYPE	NAME_EDUCATION_TYPE	NAME_FAMILY_STATUS	NAME_HOUSING_TYPE	REGION_POPULATION_RELATIVE	DAYS_BIRTH	DAYS_EMPLOYED	DAYS_REGISTRATION	DAYS_ID_PUBLISH	OWN_CAR_AGE	FLAG_MOBIL	FLAG_EMP_PHONE	FLAG_WORK_PHONE	FLAG_CONT_MOBILE	FLAG_PHONE	OCCUPATION_TYPE	CNT_FAM_MEMBERS	REGION_RATING_CLIENT	REGION_RATING_CLIENT_W_CITY	WEEKDAY_APPR_PROCESS_START	HOUR_APPR_PROCESS_START	REG_REGION_NOT_WORK_REGION	LIVE_REGION_NOT_WORK_REGION	REG_CITY_NOT_LIVE_CITY	REG_CITY_NOT_WORK_CITY	LIVE_CITY_NOT_WORK_CITY	ORGANIZATION_TYPE	EXT_SOURCE_1	EXT_SOURCE_2	EXT_SOURCE_3	OBS_30_CNT_SOCIAL_CIRCLE	OBS_60_CNT_SOCIAL_CIRCLE	DAYS_LAST_PHONE_CHANGE	FLAG_DOCUMENT_3	FLAG_DOCUMENT_5	FLAG_DOCUMENT_8	AMT_REQ_CREDIT_BUREAU_WEEK	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
47531	155054	Cash loans	M	N	N	180000.0	450000.0	NaN	450000.0	Unaccompanied	Commercial associate	Incomplete higher	Single / not married	House / apartment	0.026392	-10668	-2523	-4946.0	-3238	NaN	1	1	1	1	1	High skill tech staff	1.0	2	2	WEDNESDAY	13	0	0	0	0	0	Business Entity Type 3	NaN	0.626896	0.372334	1.0	1.0	-2.0	0	1	0	0.0	0.0	1.0	1.0
50035	157917	Cash loans	F	N	N	94500.0	450000.0	NaN	450000.0	Unaccompanied	Working	Lower secondary	Civil marriage	House / apartment	0.035792	-9027	-1270	-3640.0	-741	NaN	1	1	1	1	0	Laborers	2.0	2	2	MONDAY	20	0	0	0	0	0	Business Entity Type 1	NaN	0.727274	0.468660	0.0	0.0	-706.0	1	0	0	0.0	1.0	0.0	1.0
51594	159744	Cash loans	F	N	N	202500.0	539100.0	NaN	450000.0	Unaccompanied	Working	Secondary / secondary special	Married	House / apartment	0.046220	-17376	-3950	-11524.0	-831	NaN	1	1	0	1	0	Cooking staff	2.0	1	1	WEDNESDAY	15	0	0	1	1	1	Self-employed	0.885980	0.738370	0.452534	1.0	1.0	-199.0	1	0	0	0.0	1.0	0.0	1.0
55025	163757	Cash loans	F	N	N	162000.0	296280.0	NaN	225000.0	Unaccompanied	State servant	Higher education	Married	House / apartment	0.035792	-11329	-2040	-3195.0	-3069	NaN	1	1	0	1	0	Core staff	2.0	2	2	FRIDAY	13	0	0	1	1	1	Government	0.336803	0.566316	0.220095	0.0	0.0	-2841.0	1	0	0	0.0	1.0	0.0	4.0
59934	169487	Cash loans	M	Y	N	202500.0	360000.0	NaN	360000.0	Unaccompanied	Working	Secondary / secondary special	Single / not married	House / apartment	0.046220	-19762	-2498	-11285.0	-3305	4.0	1	1	1	1	1	Laborers	1.0	1	1	FRIDAY	15	1	1	0	1	1	Other	0.647350	0.583947	0.177704	0.0	0.0	-743.0	0	0	1	1.0	0.0	0.0	6.0

	SK_ID_CURR	TARGET	NAME_CONTRACT_TYPE	CODE_GENDER	FLAG_OWN_CAR	FLAG_OWN_REALTY	CNT_CHILDREN	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	AMT_GOODS_PRICE	NAME_TYPE_SUITE	NAME_INCOME_TYPE	NAME_EDUCATION_TYPE	NAME_FAMILY_STATUS	NAME_HOUSING_TYPE	REGION_POPULATION_RELATIVE	DAYS_BIRTH	DAYS_EMPLOYED	DAYS_REGISTRATION	DAYS_ID_PUBLISH	OWN_CAR_AGE	FLAG_MOBIL	FLAG_EMP_PHONE	FLAG_WORK_PHONE	FLAG_CONT_MOBILE	FLAG_PHONE	FLAG_EMAIL	OCCUPATION_TYPE	CNT_FAM_MEMBERS	REGION_RATING_CLIENT	REGION_RATING_CLIENT_W_CITY	WEEKDAY_APPR_PROCESS_START	HOUR_APPR_PROCESS_START	REG_REGION_NOT_LIVE_REGION	REG_REGION_NOT_WORK_REGION	LIVE_REGION_NOT_WORK_REGION	REG_CITY_NOT_LIVE_CITY	REG_CITY_NOT_WORK_CITY	LIVE_CITY_NOT_WORK_CITY	ORGANIZATION_TYPE	EXT_SOURCE_1	EXT_SOURCE_2	EXT_SOURCE_3	OBS_30_CNT_SOCIAL_CIRCLE	DEF_30_CNT_SOCIAL_CIRCLE	OBS_60_CNT_SOCIAL_CIRCLE	DEF_60_CNT_SOCIAL_CIRCLE	DAYS_LAST_PHONE_CHANGE	FLAG_DOCUMENT_2	FLAG_DOCUMENT_3	FLAG_DOCUMENT_4	FLAG_DOCUMENT_5	FLAG_DOCUMENT_6	FLAG_DOCUMENT_7	FLAG_DOCUMENT_8	FLAG_DOCUMENT_9	FLAG_DOCUMENT_10	FLAG_DOCUMENT_11	FLAG_DOCUMENT_12	FLAG_DOCUMENT_13	FLAG_DOCUMENT_14	FLAG_DOCUMENT_15	FLAG_DOCUMENT_16	FLAG_DOCUMENT_17	FLAG_DOCUMENT_18	FLAG_DOCUMENT_19	FLAG_DOCUMENT_20	FLAG_DOCUMENT_21	AMT_REQ_CREDIT_BUREAU_HOUR	AMT_REQ_CREDIT_BUREAU_DAY	AMT_REQ_CREDIT_BUREAU_WEEK	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
41982	148605	0	Revolving loans	M	N	Y	0	450000.0	675000.0	33750.0	0.0	Unaccompanied	Commercial associate	Lower secondary	Unknown	Municipal apartment	0.015221	-12396	-1161	-3265.0	-4489	0.0	1	1	1	1	1	0	Managers	NaN	2	2	THURSDAY	15	0	1	1	0	1	1	Insurance	0.628564	0.700618	NaN	3.0	0.0	3.0	0.0	-876.0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	NaN	NaN	NaN	NaN	NaN	NaN
187348	317181	0	Revolving loans	F	N	Y	0	202500.0	585000.0	29250.0	0.0	Unaccompanied	Commercial associate	Higher education	Unknown	House / apartment	0.031329	-12844	-232	-1597.0	-1571	0.0	1	1	0	1	0	0	Accountants	NaN	2	2	FRIDAY	14	0	0	0	0	0	0	Business Entity Type 2	NaN	0.645168	0.670652	1.0	0.0	1.0	0.0	-654.0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.0	0.0	0.0	0.0	0.0	1.0

	SK_ID_CURR	TARGET	CNT_CHILDREN	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	AMT_GOODS_PRICE	REGION_POPULATION_RELATIVE	DAYS_BIRTH	DAYS_EMPLOYED	DAYS_REGISTRATION	DAYS_ID_PUBLISH	OWN_CAR_AGE	FLAG_MOBIL	FLAG_EMP_PHONE	FLAG_WORK_PHONE	FLAG_CONT_MOBILE	FLAG_PHONE	FLAG_EMAIL	CNT_FAM_MEMBERS	REGION_RATING_CLIENT	REGION_RATING_CLIENT_W_CITY	HOUR_APPR_PROCESS_START	REG_REGION_NOT_LIVE_REGION	REG_REGION_NOT_WORK_REGION	LIVE_REGION_NOT_WORK_REGION	REG_CITY_NOT_LIVE_CITY	REG_CITY_NOT_WORK_CITY	LIVE_CITY_NOT_WORK_CITY	EXT_SOURCE_2	EXT_SOURCE_3	OBS_30_CNT_SOCIAL_CIRCLE	DEF_30_CNT_SOCIAL_CIRCLE	OBS_60_CNT_SOCIAL_CIRCLE	DEF_60_CNT_SOCIAL_CIRCLE	DAYS_LAST_PHONE_CHANGE	FLAG_DOCUMENT_2	FLAG_DOCUMENT_3	FLAG_DOCUMENT_4	FLAG_DOCUMENT_5	FLAG_DOCUMENT_6	FLAG_DOCUMENT_7	FLAG_DOCUMENT_8	FLAG_DOCUMENT_9	FLAG_DOCUMENT_10	FLAG_DOCUMENT_11	FLAG_DOCUMENT_12	FLAG_DOCUMENT_13	FLAG_DOCUMENT_14	FLAG_DOCUMENT_15	FLAG_DOCUMENT_16	FLAG_DOCUMENT_17	FLAG_DOCUMENT_18	FLAG_DOCUMENT_19	FLAG_DOCUMENT_20	FLAG_DOCUMENT_21	AMT_REQ_CREDIT_BUREAU_HOUR	AMT_REQ_CREDIT_BUREAU_DAY	AMT_REQ_CREDIT_BUREAU_WEEK	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
count	307508.000000	307508.00000	307508.000000	3.075080e+05	3.075080e+05	307496.000000	3.075080e+05	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.0	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	3.068490e+05	246545.000000	306487.000000	306487.000000	306487.000000	306487.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.00000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	307508.000000	265991.000000	265991.000000	265991.000000	265991.000000	265991.000000	265991.000000
mean	278181.332947	0.08073	0.417056	1.687970e+05	5.990268e+05	27108.549241	5.379139e+05	0.020868	-16037.041560	63815.673956	-4986.137584	-2994.208382	4.101851	1.0	0.819888	0.199367	0.998133	0.281066	0.056720	2.152666	2.052464	2.031521	12.063400	0.015144	0.050766	0.040656	0.078174	0.230453	0.179553	5.143916e-01	0.510852	1.422246	0.143422	1.405293	0.100050	-962.860075	0.000042	0.710030	0.000081	0.015112	0.088056	0.000189	0.081373	0.003896	0.000023	0.003912	0.000007	0.003525	0.002937	0.00121	0.009928	0.000267	0.008127	0.000595	0.000507	0.000335	0.006402	0.007000	0.034362	0.267396	0.265475	1.899978
std	102789.982905	0.27242	0.722124	2.371237e+05	4.024923e+05	14493.802393	3.696327e+05	0.013831	4363.980429	141276.312548	3522.896819	1509.449226	9.009500	0.0	0.384282	0.399525	0.043164	0.449520	0.231308	0.910683	0.509036	0.502739	3.265842	0.122127	0.219520	0.197492	0.268445	0.421123	0.383815	1.910603e-01	0.194844	2.400997	0.446700	2.379812	0.362292	826.810973	0.006502	0.453749	0.009016	0.121998	0.283377	0.013732	0.273408	0.062295	0.004771	0.062424	0.002550	0.059268	0.054110	0.03476	0.099145	0.016328	0.089781	0.024388	0.022518	0.018299	0.083849	0.110758	0.204685	0.916004	0.794057	1.869298
min	100002.000000	0.00000	0.000000	2.565000e+04	4.500000e+04	1615.500000	0.000000e+00	0.000290	-25229.000000	-17912.000000	-24672.000000	-7197.000000	0.000000	1.0	0.000000	0.000000	0.000000	0.000000	0.000000	1.000000	1.000000	1.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	8.173617e-08	0.000527	0.000000	0.000000	0.000000	0.000000	-4292.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.00000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000
25%	189146.750000	0.00000	0.000000	1.125000e+05	2.700000e+05	16524.000000	2.385000e+05	0.010006	-19682.000000	-2760.000000	-7480.000000	-4299.000000	0.000000	1.0	1.000000	0.000000	1.000000	0.000000	0.000000	2.000000	2.000000	2.000000	10.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	3.924574e-01	0.370650	0.000000	0.000000	0.000000	0.000000	-1570.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.00000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000
50%	278202.500000	0.00000	0.000000	1.471500e+05	5.135310e+05	24903.000000	4.500000e+05	0.018850	-15750.000000	-1214.000000	-4504.000000	-3254.000000	0.000000	1.0	1.000000	0.000000	1.000000	0.000000	0.000000	2.000000	2.000000	2.000000	12.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	5.659614e-01	0.535276	0.000000	0.000000	0.000000	0.000000	-757.000000	0.000000	1.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.00000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	1.000000
75%	367143.250000	0.00000	1.000000	2.025000e+05	8.086500e+05	34596.000000	6.795000e+05	0.028663	-12413.000000	-289.000000	-2010.000000	-1720.000000	5.000000	1.0	1.000000	0.000000	1.000000	1.000000	0.000000	3.000000	2.000000	2.000000	14.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	6.636171e-01	0.669057	2.000000	0.000000	2.000000	0.000000	-274.000000	0.000000	1.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.00000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	3.000000
max	456255.000000	1.00000	19.000000	1.170000e+08	4.050000e+06	258025.500000	4.050000e+06	0.072508	-7489.000000	365243.000000	0.000000	0.000000	91.000000	1.0	1.000000	1.000000	1.000000	1.000000	1.000000	20.000000	3.000000	3.000000	23.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	8.549997e-01	0.896010	348.000000	34.000000	344.000000	24.000000	0.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	1.00000	1.000000	1.000000	1.000000	1.000000	1.000000	1.000000	4.000000	9.000000	8.000000	27.000000	261.000000	25.000000

	SK_ID_CURR	NAME_CONTRACT_TYPE	CODE_GENDER	FLAG_OWN_CAR	FLAG_OWN_REALTY	CNT_CHILDREN	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	AMT_GOODS_PRICE	NAME_TYPE_SUITE	NAME_INCOME_TYPE	NAME_EDUCATION_TYPE	NAME_FAMILY_STATUS	NAME_HOUSING_TYPE	REGION_POPULATION_RELATIVE	DAYS_BIRTH	DAYS_EMPLOYED	DAYS_REGISTRATION	DAYS_ID_PUBLISH	OWN_CAR_AGE	FLAG_MOBIL	FLAG_EMP_PHONE	FLAG_WORK_PHONE	FLAG_CONT_MOBILE	FLAG_PHONE	OCCUPATION_TYPE	CNT_FAM_MEMBERS	REGION_RATING_CLIENT	REGION_RATING_CLIENT_W_CITY	WEEKDAY_APPR_PROCESS_START	HOUR_APPR_PROCESS_START	ORGANIZATION_TYPE	EXT_SOURCE_2	EXT_SOURCE_3	OBS_30_CNT_SOCIAL_CIRCLE	DEF_30_CNT_SOCIAL_CIRCLE	OBS_60_CNT_SOCIAL_CIRCLE	DEF_60_CNT_SOCIAL_CIRCLE	DAYS_LAST_PHONE_CHANGE	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
35656	141289	Revolving loans	XNA	Y	Y	0	207000.0	382500.0	19125.0	337500.0	Unaccompanied	Working	Secondary / secondary special	Married	Municipal apartment	0.020713	20232.0	10044.0	10024.0	3537.0	13.0	1	1	1	1	1	NaN	2.0	3	3	TUESDAY	10	Business Entity Type 1	0.295998	0.461482	0.0	0.0	0.0	0.0	-286.0	0.0	0.0	1.0
38565	144669	Revolving loans	XNA	N	Y	2	157500.0	270000.0	13500.0	225000.0	Family	Working	Secondary / secondary special	Married	House / apartment	0.026392	13717.0	2797.0	2241.0	4659.0	0.0	1	1	1	1	1	Low-skill Laborers	4.0	2	2	FRIDAY	16	Industry: type 3	0.709205	0.310818	0.0	0.0	0.0	0.0	-493.0	3.0	0.0	4.0
83380	196708	Revolving loans	XNA	N	Y	1	135000.0	405000.0	20250.0	225000.0	Unaccompanied	Working	Higher education	Married	House / apartment	0.035792	10647.0	1228.0	183.0	1671.0	0.0	1	1	1	1	1	Core staff	3.0	2	2	THURSDAY	15	Kindergarten	0.659185	0.076984	7.0	1.0	7.0	1.0	-851.0	0.0	0.0	3.0
189637	319880	Revolving loans	XNA	Y	Y	0	247500.0	540000.0	27000.0	900000.0	Unaccompanied	Commercial associate	Incomplete higher	Civil marriage	House / apartment	0.035792	9649.0	2293.0	4099.0	2326.0	8.0	1	1	1	1	1	NaN	2.0	2	2	FRIDAY	15	Medicine	0.658620	0.360613	10.0	4.0	10.0	4.0	-1681.0	0.0	1.0	6.0

CODE_GENDER	F	M
NAME_INCOME_TYPE
Commercial associate	613418.006402	599681.603679
Maternity leave	929250.000000	NaN
Pensioner	558498.129737	556644.763194
State servant	614061.717237	616406.753731
Unemployed	653250.000000	546750.000000
Working	537034.975876	533821.403301

TARGET	0	1
NAME_EDUCATION_TYPE
Academic degree	719290.844720	950245.500000
Higher education	692439.813734	645981.010851
Incomplete higher	569727.478041	534740.986239
Lower secondary	490695.314303	481589.525180
Secondary / secondary special	574033.649640	542263.182160

	Column1	Column2	Correlation
28	AMT_GOODS_PRICE	AMT_CREDIT	0.982854
29	AMT_GOODS_PRICE	AMT_ANNUITY	0.752891
19	AMT_ANNUITY	AMT_CREDIT	0.752195
59	DAYS_EMPLOYED	DAYS_BIRTH	0.582185
68	DAYS_REGISTRATION	DAYS_BIRTH	0.289114
77	DAYS_ID_PUBLISH	DAYS_BIRTH	0.252863
78	DAYS_ID_PUBLISH	DAYS_EMPLOYED	0.229090
69	DAYS_REGISTRATION	DAYS_EMPLOYED	0.192455
48	DAYS_BIRTH	AMT_GOODS_PRICE	0.135516
46	DAYS_BIRTH	AMT_CREDIT	0.135316

Problem description¶

Aim : To identify consumers/customers capable of repaying the loan are not being rejected.¶

Data understanding and available files¶

We have (307511, 122) rows and columns, To decide which columns are feasible for risk analytics, we have to know what they are....data!!¶

Based on the data information, we have a glance of data type, info and typical statistic info, next step is to clean the data and choose columns of interest further to analyze the data¶

As the living apartment space and parameters has no direct relationship for loan application process and columns are having lot of missing entries > 40%, we can drop those columns from 44 to 90 indexes¶

Dropping the columns from 44 to 90¶

We have 65% of missing entries with 'OWN CAR AGE ' column, may be people have cars let check¶

Now we have dropped some rows/columns and imputed some rows/columns understanding the data, now there are some missing entries that's not necessary for imputation, lets explore the data for any errors , standardization and visualize it clearly...¶

It appears like we have data of certain columns with negative values, we have to correct these errors¶

Lets analyse the data and perform distinct analysis for the variables based on data types¶

As we can see most of loan processed data is for cash loans (90%) relative Revolving loans (9.5%)¶

As we can observe the client with solid payment issues are around 8% rest of the 92% are repaying well, there are imbalances may be we will understand why certain people are having payment issues by separating them into another dataframe¶

Most of the people dont owns cars....¶

Most of the people own real estate , could be the issues for payment? let' see¶

Most of the loans processed are with unaccompanied clients¶

Most of the people are from working profiles > 50% for loan applications¶

Most of the observations around 70% are with Secondary/Secondary special education¶

Most of the loan applications were done in start of the week on average.¶

Based on the box plot for income total, lets perfom binning to identify any outliers better and get better understanding¶

we see that people have more numbers for very low and high salaries relative to other groups....¶

People have more counts for low and high profile salaries i.e. in synch with amount credited¶

Checking on columns that have days employed.or starting with days..etc so that we know people with high numbers of days being employed¶

One of the value 117M is definitely an outlier¶

The value above 250K is definitely an outlier¶

Values above 300000 is defintely outlier¶

Now to understand why target-1 is having payment issues and target-0 is runnings with no issues, we have split the data into two groups to analyze more better¶

Dividing the dataset into two groups based on target values¶

We will start with univariate analysis of target-1 group¶

Observations¶

Observations¶

Observations¶

Observations¶

Observations¶

Observations¶

Observations¶

Observations¶

Observation from categorical/object columns (Univariate analysis)¶

Observations¶

Observations¶

Bi-variate analysis (Checking on two variables at a time)¶

Observations¶

Top 10 variables of correlation as above for the people with payment difficulties.¶